<?php
namespace app\index\controller;
use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use app\index\validate\Valid;
use PHPExcel;
use PHPExcel_Writer_Excel5;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Fill;
use PHPExcel_Style_Border;
use PHPExcel_Cell_DataType;
class Tongjisheng extends Common
{
        public function shengmbout($cid,$zid){
        $nian=db("cg_cainian")->where(["id"=>$cid])->value("title");
        $kaohe1=db("cg_kaoheqi1")->where(["cid"=>$cid])->select();

        $where="isdel=0 ";
        if(!$zid){
            $zid=db("cg_market_provice")->where($where)->value("id");
        }else{
            $zid=implode(',',explode('-',$zid));
        }
        $where.="and id in ($zid) ";
        $zarr=[];
        if($zid){
            $zarr=db("cg_market_provice")->where($where)->field("id,title")->select();
        }

        for($i=0;$i<count($zarr);$i++){
            $zarr[$i]["zyjxl"]=0;$zarr[$i]["zyjfy"]=0;$zarr[$i]["zxldc"]=0;$zarr[$i]["zfysq"]=0;$zarr[$i]["zyjfyl"]=0;
            $zarr[$i]["zsjfyl"]=0;$zarr[$i]["zdcl"]='-';
            for($j=0;$j<count($kaohe1);$j++){
                $ed=$kaohe1[$j]["endtime"]+3600*24;

                //预计销量
                $yjxl=0;
                $tmpxl=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.sheng={$zarr[$i]["id"]} and t.type_id=5 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe1[$j]["id"]}")->sum("feiyong");
                if($tmpxl){
                    $yjxl=round($tmpxl,2);
                }
                $zarr[$i]["yjxl"][$j]=$yjxl;
                $zarr[$i]["zyjxl"]+=$yjxl;
                //预计费用预算
                $yjfy=0;
                $tmpfy=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.sheng={$zarr[$i]["id"]} and t.type_id=2 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe1[$j]["id"]}")->sum("feiyong");
                if($tmpfy){
                    $yjfy=round($tmpfy,2);
                }
                $zarr[$i]["yjfy"][$j]=$yjfy;
                $zarr[$i]["zyjfy"]+=$yjfy;
                //销量达成
                $xldc=0;
                $tmpxdc=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")->where("j.provice={$zarr[$i]["id"]} and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe1[$j]["starttime"]} and td.start<{$ed}")->field("totalExpectedSales,fb_shijixiaoliang")->select();
                foreach ($tmpxdc as $k => $v) {
                    # code...
                    if($v["fb_shijixiaoliang"]){
                        $xldc+=$v["fb_shijixiaoliang"];
                    }else{
                        $xldc+=$v["totalExpectedSales"];
                    }
                }
                $zarr[$i]["xldc"][$j]=round($xldc,2);
                $zarr[$i]["zxldc"]+=round($xldc,2);
                //费用申请
                $fysq=0;
                $tmpsq=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")->where("j.provice={$zarr[$i]["id"]} and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe1[$j]["starttime"]} and td.start<$ed")->field("yjsjtrfy,fb_shijitourufeiyong")->select();
                foreach ($tmpsq as $k => $v) {
                    # code...
                    if($v["fb_shijitourufeiyong"]){
                        $fysq+=$v["fb_shijitourufeiyong"];
                    }else{
                        $fysq+=$v["yjsjtrfy"];
                    }
                }
                $zarr[$i]["fysq"][$j]=round($fysq,2);
                $zarr[$i]["zfysq"]+=round($fysq,2);
                if($zarr[$i]["yjxl"][$j]){
                    $zarr[$i]["yjfyl"][$j]=round($zarr[$i]["yjfy"][$j]/$zarr[$i]["yjxl"][$j]*100,2);
                }else{
                    $zarr[$i]["yjfyl"][$j]=0;
                }

                if($zarr[$i]["xldc"][$j]){
                    $zarr[$i]["sjfyl"][$j]=round($zarr[$i]["fysq"][$j]/$zarr[$i]["xldc"][$j]*100,2);
                }else{
                    $zarr[$i]["sjfyl"][$j]=0;
                }

                if($zarr[$i]["yjxl"][$j]){
                    $zarr[$i]["dcl"][$j]=round($zarr[$i]["xldc"][$j]/$zarr[$i]["yjxl"][$j]*100,2);
                }else{
                    $zarr[$i]["dcl"][$j]='-';
                }
            }
            if($zarr[$i]["zyjxl"]){
                $zarr[$i]["zyjfyl"]=round($zarr[$i]["zyjfy"]/$zarr[$i]["zyjxl"]*100,2);
            }
            if($zarr[$i]["zxldc"]){
                $zarr[$i]["zsjfyl"]=round($zarr[$i]["zfysq"]/$zarr[$i]["zxldc"]*100,2);
            }
            if($zarr[$i]["zyjxl"]){
                $zarr[$i]["zdcl"]=round($zarr[$i]["zxldc"]/$zarr[$i]["zyjxl"]*100,2);
            }
        }
         //excel
        $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();
        $title=$nian."财年省份目标达成报表";
        Vendor("PHPExcel");
        Vendor("PHPExcel.Writer.Excel5");
        $objPHPExcel= new PHPExcel();
        $objProps=$objPHPExcel->getProperties();
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
                
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle($title);
       
              #标题
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(20);

       

        
        $objPHPExcel->getActiveSheet()->mergeCells('A1:G1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "$title");
              //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

            #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:G2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
        #####地域信息
        $objPHPExcel->getActiveSheet()->mergeCells('A3:G3');
                 //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
         //字体
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setSize(10);

        $str="包含省份:";
        if($zarr){
            $tmparr=[];
            foreach ($zarr as $k => $v) {
                # code...
                $tmparr[]=$v["title"];
            }
            $str.=implode(',',$tmparr);
        }else{
            $str.="无";
        }
        $objPHPExcel->getActiveSheet()->setCellValue('A3',$str);

         $objPHPExcel->getActiveSheet()->getStyle('4')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A4:G4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A4:G4')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A4:G4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A4:G4')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A4',"省份名称");
        $objPHPExcel->getActiveSheet()->setCellValue('C4',"第一季度");
        $objPHPExcel->getActiveSheet()->setCellValue('D4',"第二季度");
        $objPHPExcel->getActiveSheet()->setCellValue('E4',"第三季度");
        $objPHPExcel->getActiveSheet()->setCellValue('F4',"第四季度");
        $objPHPExcel->getActiveSheet()->setCellValue('G4',"合计");
        $jarr=$zarr;
        $h=4;
        for($i=0;$i<count($jarr);$i++){
            $h++;
            $objPHPExcel->getActiveSheet()->mergeCells('A'.$h.':A'.($h+6));
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$jarr[$i]["title"],PHPExcel_Cell_DataType::TYPE_STRING);
             //垂直剧中
            $objPHPExcel->getActiveSheet()->getStyle('A'.$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"预计销售目标(元)",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$jarr[$i]["yjxl"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$jarr[$i]["yjxl"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$jarr[$i]["yjxl"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$jarr[$i]["yjxl"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$jarr[$i]["zyjxl"],PHPExcel_Cell_DataType::TYPE_STRING);
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"预计费用预算(元)",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$jarr[$i]["yjfy"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$jarr[$i]["yjfy"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$jarr[$i]["yjfy"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$jarr[$i]["yjfy"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$jarr[$i]["zyjfy"],PHPExcel_Cell_DataType::TYPE_STRING);
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"实际销售达成(元)",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$jarr[$i]["xldc"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$jarr[$i]["xldc"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$jarr[$i]["xldc"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$jarr[$i]["xldc"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$jarr[$i]["zxldc"],PHPExcel_Cell_DataType::TYPE_STRING);
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"实际费用申请(元)",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$jarr[$i]["fysq"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$jarr[$i]["fysq"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$jarr[$i]["fysq"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$jarr[$i]["fysq"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$jarr[$i]["zfysq"],PHPExcel_Cell_DataType::TYPE_STRING);
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"预计费用率",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$jarr[$i]["yjfyl"][0].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$jarr[$i]["yjfyl"][1].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$jarr[$i]["yjfyl"][2].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$jarr[$i]["yjfyl"][3].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$jarr[$i]["zyjfyl"].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"实际费用率",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$jarr[$i]["sjfyl"][0].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$jarr[$i]["sjfyl"][1].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$jarr[$i]["sjfyl"][2].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$jarr[$i]["sjfyl"][3].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$jarr[$i]["zsjfyl"].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"实际达成率",PHPExcel_Cell_DataType::TYPE_STRING);
            if($jarr[$i]["dcl"][0])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$jarr[$i]["dcl"][0].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
           if($jarr[$i]["dcl"][1])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$jarr[$i]["dcl"][1].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
            if($jarr[$i]["dcl"][2])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$jarr[$i]["dcl"][2].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
            if($jarr[$i]["dcl"][3])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$jarr[$i]["dcl"][3].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
            if($jarr[$i]["zdcl"])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$jarr[$i]["zdcl"].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
            
          
        }
           $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:G'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }
    //省目标
    public function shengmb($cid='',$zid=''){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $nian=db("cg_cainian")->where(["id"=>$cid])->value("title");
        $this->assign("nian",$nian);
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $kaohe1=db("cg_kaoheqi1")->where(["cid"=>$cid])->select();

        $this->assign("kaohe1",$kaohe1);
         $where="isdel=0 ";
        if(!$zid){
            $zid=db("cg_market_provice")->where($where)->value("id");
        }
        $where.="and id in ($zid) ";
        $zarr=[];
        if($zid){
            $zarr=db("cg_market_provice")->where($where)->field("id,title")->select();
        }
          for($i=0;$i<count($zarr);$i++){
            $zarr[$i]["zyjxl"]=0;$zarr[$i]["zyjfy"]=0;$zarr[$i]["zxldc"]=0;$zarr[$i]["zfysq"]=0;$zarr[$i]["zyjfyl"]=0;
            $zarr[$i]["zsjfyl"]=0;$zarr[$i]["zdcl"]='-';
            for($j=0;$j<count($kaohe1);$j++){
                $ed=$kaohe1[$j]["endtime"]+3600*24;
                //预计销量
                $yjxl=0;
                $tmpxl=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.sheng={$zarr[$i]["id"]} and t.type_id=5 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe1[$j]["id"]}")->sum("feiyong");
                if($tmpxl){
                    $yjxl=round($tmpxl,2);
                }
                $zarr[$i]["yjxl"][$j]=$yjxl;
                $zarr[$i]["zyjxl"]+=$yjxl;
                //预计费用预算
                $yjfy=0;
                $tmpfy=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.sheng={$zarr[$i]["id"]} and t.type_id=2 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe1[$j]["id"]}")->sum("feiyong");
                if($tmpfy){
                    $yjfy=round($tmpfy,2);
                }
                $zarr[$i]["yjfy"][$j]=$yjfy;
                $zarr[$i]["zyjfy"]+=$yjfy;
                //销量达成
                $xldc=0;
                $tmpxdc=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")->where("j.provice={$zarr[$i]["id"]} and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe1[$j]["starttime"]} and td.start<{$ed}")->field("totalExpectedSales,fb_shijixiaoliang")->select();
                //echo db("cg_ti")->getLastSql();exit;
                foreach ($tmpxdc as $k => $v) {
                    # code...
                    if($v["fb_shijixiaoliang"]){
                        $xldc+=$v["fb_shijixiaoliang"];
                    }else{
                        $xldc+=$v["totalExpectedSales"];
                    }
                }
                $zarr[$i]["xldc"][$j]=round($xldc,2);
                $zarr[$i]["zxldc"]+=round($xldc,2);
                //费用申请
                $fysq=0;
                $tmpsq=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")->where("j.provice={$zarr[$i]["id"]} and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe1[$j]["starttime"]} and td.start<{$ed}")->field("yjsjtrfy,fb_shijitourufeiyong")->select();
                foreach ($tmpsq as $k => $v) {
                    # code...
                    if($v["fb_shijitourufeiyong"]){
                        $fysq+=$v["fb_shijitourufeiyong"];
                    }else{
                        $fysq+=$v["yjsjtrfy"];
                    }
                }
                $zarr[$i]["fysq"][$j]=round($fysq,2);
                $zarr[$i]["zfysq"]+=round($fysq,2);
                if($zarr[$i]["yjxl"][$j]){
                    $zarr[$i]["yjfyl"][$j]=round($zarr[$i]["yjfy"][$j]/$zarr[$i]["yjxl"][$j]*100,2);
                }else{
                    $zarr[$i]["yjfyl"][$j]=0;
                }

                if($zarr[$i]["xldc"][$j]){
                    $zarr[$i]["sjfyl"][$j]=round($zarr[$i]["fysq"][$j]/$zarr[$i]["xldc"][$j]*100,2);
                }else{
                    $zarr[$i]["sjfyl"][$j]=0;
                }

                if($zarr[$i]["yjxl"][$j]){
                    $zarr[$i]["dcl"][$j]=round($zarr[$i]["xldc"][$j]/$zarr[$i]["yjxl"][$j]*100,2);
                }else{
                    $zarr[$i]["dcl"][$j]='-';
                }
            }
            if($zarr[$i]["zyjxl"]){
                $zarr[$i]["zyjfyl"]=round($zarr[$i]["zyjfy"]/$zarr[$i]["zyjxl"]*100,2);
            }
            if($zarr[$i]["zxldc"]){
                $zarr[$i]["zsjfyl"]=round($zarr[$i]["zfysq"]/$zarr[$i]["zxldc"]*100,2);
            }
            if($zarr[$i]["zyjxl"]){
                $zarr[$i]["zdcl"]=round($zarr[$i]["zxldc"]/$zarr[$i]["zyjxl"]*100,2);
            }
        }
        $this->assign("zarr",$zarr);

        return $this->fetch();
    }
    //省决策依据
    public function shengjc($cid="",$sid=[]){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $sheng=db("cg_market_provice")->where("isdel=0")->select();
        $this->assign("sheng",$sheng);
        $kaohe2=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->order("k2.starttime")->where("k1.cid=$cid")->field("k2.id,k2.title,k2.starttime,k2.endtime,k1.id as k1id,k1.starttime as k1start,k1.endtime as k1end")->select();
        $srr=$sid;
        $this->assign("srr",$srr);
        if($sid){
            $sid=implode(',',$sid);
            $sarr=db("cg_market_provice")->where("id in ($sid)")->field("id,title")->select();
        }else{
            $sarr=[];
        }
        for($i=0;$i<count($sarr);$i++){
            $sarr[$i]["zyjxl"]=0;$sarr[$i]["zyjfy"]=0;$sarr[$i]["zxldc"]=0;$sarr[$i]["zfysq"]=0;$sarr[$i]["zyjfyl"]=0;$sarr[$i]["zsjfyl"]=0;$sarr[$i]["zdcl"]=0;$sarr[$i]["zky"]=0;
            $sarr[$i]["zbili"]=0;
            for($j=0;$j<count($kaohe2);$j++){
                $kaohe2[$j]["endtime"]=$kaohe2[$j]["endtime"]+3600*24;
                $k1ts=($kaohe2[$j]["k1end"]-$kaohe2[$j]["k1start"])/3600/24+1;
                $k2ts=($kaohe2[$j]["endtime"]-$kaohe2[$j]["starttime"])/3600/24+1;
                //预计销量
                $yjxl=0;
                $tmpxl=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.sheng={$sarr[$i]["id"]} and t.type_id=5 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.erjikaoheqi={$kaohe2[$j]["id"]}")->sum("feiyong");
                if($tmpxl){
                    $yjxl+=round($tmpxl,2);
                }
                $tmpxl2=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.sheng={$sarr[$i]["id"]} and t.type_id=5 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe2[$j]["k1id"]} and td.erjikaoheqi is null")->sum("feiyong");
                if($tmpxl2){
                    $yjxl+=round($tmpxl2/$k1ts*$k2ts,2);
                }
                $sarr[$i]["yjxl"][$j]=$yjxl;
                $sarr[$i]["zyjxl"]+=$yjxl;
                //预计费用
                $yjfy=0;
                $tmpfy=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.sheng={$sarr[$i]["id"]} and t.type_id=2 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.erjikaoheqi={$kaohe2[$j]["id"]}")->sum("feiyong");
                if($tmpfy){
                    $yjfy+=round($tmpfy,2);
                }
                $tmpfy2=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.sheng={$sarr[$i]["id"]} and t.type_id=2 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe2[$j]["k1id"]} and td.erjikaoheqi is null")->sum("feiyong");
                 if($tmpfy2){
                    $yjfy+=round($tmpfy2/$k1ts*$k2ts,2);
                }
                $sarr[$i]["yjfy"][$j]=$yjfy;
                $sarr[$i]["zyjfy"]+=$yjfy;
                //实际销量
                $xldc=0;
                $tmpxdc=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")->where("j.provice={$sarr[$i]["id"]} and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe2[$j]["starttime"]} and td.start<{$kaohe2[$j]["endtime"]}")->field("totalExpectedSales,fb_shijixiaoliang")->select();
                foreach ($tmpxdc as $k => $v) {
                    # code...
                    if($v["fb_shijixiaoliang"]){
                        $xldc+=$v["fb_shijixiaoliang"];
                    }else{
                        $xldc+=$v["totalExpectedSales"];
                    }
                }
                $sarr[$i]["xldc"][$j]=round($xldc,2);
                $sarr[$i]["zxldc"]+=round($xldc,2);
                //实际费用申请
                $fysq=0;
                $tmpsq=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")->where("j.provice={$sarr[$i]["id"]} and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe2[$j]["starttime"]} and td.start<{$kaohe2[$j]["endtime"]}")->field("yjsjtrfy,fb_shijitourufeiyong")->select();
                foreach ($tmpsq as $k => $v) {
                    # code...
                    if($v["fb_shijitourufeiyong"]){
                        $fysq+=$v["fb_shijitourufeiyong"];
                    }else{
                        $fysq+=$v["yjsjtrfy"];
                    }
                }
                $sarr[$i]["fysq"][$j]=round($fysq,2);
                $sarr[$i]["zfysq"]+=round($fysq,2);
                //预计费用率
                if($sarr[$i]["yjxl"][$j]){
                    $sarr[$i]["yjfyl"][$j]=round($sarr[$i]["yjfy"][$j]/$sarr[$i]["yjxl"][$j]*100,2);
                }else{
                    $sarr[$i]["yjfyl"][$j]=0;
                }
                //实际费用率
                if($sarr[$i]["xldc"][$j]){
                    $sarr[$i]["sjfyl"][$j]=round($sarr[$i]["fysq"][$j]/$sarr[$i]["xldc"][$j]*100,2);
                }else{
                    $sarr[$i]["sjfyl"][$j]=0;
                }
                //销量达成率
                if($sarr[$i]["yjxl"][$j]){
                    $sarr[$i]["dcl"][$j]=round($sarr[$i]["xldc"][$j]/$sarr[$i]["yjxl"][$j]*100,2);
                }else{
                    $sarr[$i]["dcl"][$j]='-';
                }
                //可用
                $sarr[$i]["ky"][$j]=$sarr[$i]["yjfy"][$j]-$sarr[$i]["fysq"][$j];
                //费用比例
                if($sarr[$i]["yjfy"][$j]){
                    $sarr[$i]["bili"][$j]=round($sarr[$i]["fysq"][$j]/$sarr[$i]["yjfy"][$j]*100,2);
                }else{
                    $sarr[$i]["bili"][$j]=0;
                }
            }
            if($sarr[$i]["zyjxl"]){
                $sarr[$i]["zyjfyl"]=round($sarr[$i]["zyjfy"]/$sarr[$i]["zyjxl"]*100,2);
            }
             if($sarr[$i]["zxldc"]){
                $sarr[$i]["zsjfyl"]=round($sarr[$i]["zfysq"]/$sarr[$i]["zxldc"]*100,2);
            }
            if($sarr[$i]["zyjxl"]){
                $sarr[$i]["zdcl"]=round($sarr[$i]["zxldc"]/$sarr[$i]["zyjxl"]*100,2);
            }
            $sarr[$i]["zky"]=$sarr[$i]["zyjfy"]-$sarr[$i]["zfysq"];
            if($sarr[$i]["zyjfy"]){
                $sarr[$i]["zbili"]=round($sarr[$i]["zfysq"]/$sarr[$i]["zyjfy"]*100,2);
            }
        }
        $this->assign("kaohe2",$kaohe2);
        $this->assign("sarr",$sarr);
        return $this->fetch();
    }
    //各省投入情况
    public function shengalltr($cid=""){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $sarr=db("cg_market_provice")->where("isdel=0")->select();
        $kaohe2=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->order("k2.starttime")->where("k1.cid=$cid")->field("k2.id,k2.title,k2.starttime,k2.endtime,k1.id as k1id,k1.starttime as k1start,k1.endtime as k1end")->select();
        $zyj=0;$zsj=0;$zong=0;
        for($i=0;$i<count($sarr);$i++){
            $sarr[$i]["zyjfy"]=0;$sarr[$i]["zfysq"]=0;
            for($j=0;$j<count($kaohe2);$j++){
                $kaohe2[$j]["endtime"]=$kaohe2[$j]["endtime"]+3600*24;
                $k1ts=($kaohe2[$j]["k1end"]-$kaohe2[$j]["k1start"])/3600/24+1;
                $k2ts=($kaohe2[$j]["endtime"]-$kaohe2[$j]["starttime"])/3600/24+1;
                 //预计费用
                $yjfy=0;
                $tmpfy=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.sheng={$sarr[$i]["id"]} and t.type_id=2 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.erjikaoheqi={$kaohe2[$j]["id"]}")->sum("feiyong");
                if($tmpfy){
                    $yjfy+=round($tmpfy,2);
                }
                $tmpfy2=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.sheng={$sarr[$i]["id"]} and t.type_id=2 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe2[$j]["k1id"]} and td.erjikaoheqi is null")->sum("feiyong");
                 if($tmpfy2){
                    $yjfy+=round($tmpfy2/$k1ts*$k2ts,2);
                }
                $sarr[$i]["yjfy"][$j]=$yjfy;
                $sarr[$i]["zyjfy"]+=$yjfy;
                 //实际费用申请
                $fysq=0;
                $tmpsq=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")->where("j.provice={$sarr[$i]["id"]} and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe2[$j]["starttime"]} and td.start<{$kaohe2[$j]["endtime"]}")->field("yjsjtrfy,fb_shijitourufeiyong")->select();
                foreach ($tmpsq as $k => $v) {
                    # code...
                    if($v["fb_shijitourufeiyong"]){
                        $fysq+=$v["fb_shijitourufeiyong"];
                    }else{
                        $fysq+=$v["yjsjtrfy"];
                    }
                }
                $sarr[$i]["fysq"][$j]=round($fysq,2);
                $sarr[$i]["zfysq"]+=round($fysq,2);
            }
            $zyj+=$sarr[$i]["zyjfy"];
            $zsj+=$sarr[$i]["zfysq"];
            if(count($kaohe2)){
                $sarr[$i]["ayjfy"]=round($sarr[$i]["zyjfy"]/count($kaohe2),2);
                $sarr[$i]["afysq"]=round($sarr[$i]["zfysq"]/count($kaohe2),2);
            }else{
                $sarr[$i]["ayjfy"]=0;
                $sarr[$i]["afysq"]=0;
            }
            
        }
        $zong=$zyj-$zsj;$avg=0;
        if(count($kaohe2)){
            $avg=round($zong/count($kaohe2),2);
        }
        foreach ($sarr as $k => $v) {
            # code...
            if($zyj){
                $sarr[$k]["byjfy"]=round($v["zyjfy"]/$zyj*100,2);
            }else{
                $sarr[$k]["byjfy"]=0;
            }
            if($zsj){
                $sarr[$k]["bfysq"]=round($v["zfysq"]/$zsj*100,2);
            }else{
                $sarr[$k]["bfysq"]=0;
            }
        }
        $this->assign("avg",$avg);
        $this->assign("zong",$zong);
        $this->assign("kaohe2",$kaohe2);
        $this->assign("sarr",$sarr);
        return $this->fetch();
    }
    public function shengalltrdc($cid){
        $nian=db("cg_cainian")->where("id=$cid")->value("title");
        $sarr=db("cg_market_provice")->where("isdel=0")->select();
        $kaohe2=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->order("k2.starttime")->where("k1.cid=$cid")->field("k2.id,k2.title,k2.starttime,k2.endtime,k1.id as k1id,k1.starttime as k1start,k1.endtime as k1end")->select();
        $zyj=0;$zsj=0;$zong=0;
        for($i=0;$i<count($sarr);$i++){
            $sarr[$i]["zyjfy"]=0;$sarr[$i]["zfysq"]=0;
            for($j=0;$j<count($kaohe2);$j++){
                $kaohe2[$j]["endtime"]=$kaohe2[$j]["endtime"]+3600*24;
                $k1ts=($kaohe2[$j]["k1end"]-$kaohe2[$j]["k1start"])/3600/24+1;
                $k2ts=($kaohe2[$j]["endtime"]-$kaohe2[$j]["starttime"])/3600/24+1;
                 //预计费用
                $yjfy=0;
                $tmpfy=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.sheng={$sarr[$i]["id"]} and t.type_id=2 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.erjikaoheqi={$kaohe2[$j]["id"]}")->sum("feiyong");
                if($tmpfy){
                    $yjfy+=round($tmpfy,2);
                }
                $tmpfy2=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.sheng={$sarr[$i]["id"]} and t.type_id=2 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe2[$j]["k1id"]} and td.erjikaoheqi is null")->sum("feiyong");
                 if($tmpfy2){
                    $yjfy+=round($tmpfy2/$k1ts*$k2ts,2);
                }
                $sarr[$i]["yjfy"][$j]=$yjfy;
                $sarr[$i]["zyjfy"]+=$yjfy;
                 //实际费用申请
                $fysq=0;
                $tmpsq=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")->where("j.provice={$sarr[$i]["id"]} and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe2[$j]["starttime"]} and td.start<{$kaohe2[$j]["endtime"]}")->field("yjsjtrfy,fb_shijitourufeiyong")->select();
                foreach ($tmpsq as $k => $v) {
                    # code...
                    if($v["fb_shijitourufeiyong"]){
                        $fysq+=$v["fb_shijitourufeiyong"];
                    }else{
                        $fysq+=$v["yjsjtrfy"];
                    }
                }
                $sarr[$i]["fysq"][$j]=round($fysq,2);
                $sarr[$i]["zfysq"]+=round($fysq,2);
            }
            $zyj+=$sarr[$i]["zyjfy"];
            $zsj+=$sarr[$i]["zfysq"];
            if(count($kaohe2)){
                $sarr[$i]["ayjfy"]=round($sarr[$i]["zyjfy"]/count($kaohe2),2);
                $sarr[$i]["afysq"]=round($sarr[$i]["zfysq"]/count($kaohe2),2);
            }else{
                $sarr[$i]["ayjfy"]=0;
                $sarr[$i]["afysq"]=0;
            }
            
        }
        $zong=$zyj-$zsj;$avg=0;
        if(count($kaohe2)){
            $avg=round($zong/count($kaohe2),2);
        }
        foreach ($sarr as $k => $v) {
            # code...
            if($zyj){
                $sarr[$k]["byjfy"]=round($v["zyjfy"]/$zyj*100,2);
            }else{
                $sarr[$k]["byjfy"]=0;
            }
            if($zsj){
                $sarr[$k]["bfysq"]=round($v["zfysq"]/$zsj*100,2);
            }else{
                $sarr[$k]["bfysq"]=0;
            }
        }
        $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();

        Vendor("PHPExcel");


        Vendor("PHPExcel.Writer.Excel5");

        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new PHPExcel();


        $objProps = $objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("{$nian}各省份年总费用投入情况");

        #################总标题

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:Q1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "{$nian}各省份年总费用投入情况");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
        #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:Q2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "(本表单由软件导出，导出时间：" . date("Y-m-d H:i:s") . "；导出人：{$users["em_name"]})");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:Q3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:Q3')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:Q3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:Q3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"省份");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"项目");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"1月");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"2月");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"3月");
        $objPHPExcel->getActiveSheet()->setCellValue('F3',"4月");
        $objPHPExcel->getActiveSheet()->setCellValue('G3',"5月");
        $objPHPExcel->getActiveSheet()->setCellValue('H3',"6月");
        $objPHPExcel->getActiveSheet()->setCellValue('I3',"7月");
        $objPHPExcel->getActiveSheet()->setCellValue('J3',"8月");
        $objPHPExcel->getActiveSheet()->setCellValue('K3',"9月");
        $objPHPExcel->getActiveSheet()->setCellValue('L3',"10月");
        $objPHPExcel->getActiveSheet()->setCellValue('M3',"11月");
        $objPHPExcel->getActiveSheet()->setCellValue('N3',"12月");
        $objPHPExcel->getActiveSheet()->setCellValue('O3',"小计");
        $objPHPExcel->getActiveSheet()->setCellValue('P3',"平均");
        $objPHPExcel->getActiveSheet()->setCellValue('Q3',"占比");

        $h=3;
        for($i=0;$i<count($sarr);$i++){
            $h++;$endh=$h+1;
            $objPHPExcel->getActiveSheet()->mergeCells('A'.$h.':A'.$endh);
              //垂直剧中
            $objPHPExcel->getActiveSheet()->getStyle('A'.$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$sarr[$i]["title"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"费用预算",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$sarr[$i]["yjfy"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$sarr[$i]["yjfy"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$sarr[$i]["yjfy"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$sarr[$i]["yjfy"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$sarr[$i]["yjfy"][4],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$sarr[$i]["yjfy"][5],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$sarr[$i]["yjfy"][6],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$sarr[$i]["yjfy"][7],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$sarr[$i]["yjfy"][8],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$sarr[$i]["yjfy"][9],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$sarr[$i]["yjfy"][10],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$sarr[$i]["yjfy"][11],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$sarr[$i]["zyjfy"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$sarr[$i]["ayjfy"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('Q'.$h,$sarr[$i]["byjfy"]."%",PHPExcel_Cell_DataType::TYPE_STRING);
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"实际投入",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$sarr[$i]["fysq"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$sarr[$i]["fysq"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$sarr[$i]["fysq"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$sarr[$i]["fysq"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$sarr[$i]["fysq"][4],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$sarr[$i]["fysq"][5],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$sarr[$i]["fysq"][6],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$sarr[$i]["fysq"][7],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$sarr[$i]["fysq"][8],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$sarr[$i]["fysq"][9],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$sarr[$i]["fysq"][10],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$sarr[$i]["fysq"][11],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$sarr[$i]["zfysq"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$sarr[$i]["afysq"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('Q'.$h,$sarr[$i]["bfysq"]."%",PHPExcel_Cell_DataType::TYPE_STRING);
        }
        $h++;
        $objPHPExcel->getActiveSheet()->mergeCells('A'.$h.':N'.$h);
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A'.$h)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,"总计",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$zong,PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$avg,PHPExcel_Cell_DataType::TYPE_STRING);
        $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:Q'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(20);
           $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(20);
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');  
    }
    //省明细
    public function shengdet($sid="",$cid=""){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        
        $cn=db("cg_cainian")->where(["id"=>$cid])->find();
        $cn["endtime"]=$cn["endtime"]+3600*24;
        $this->assign("cn",$cn);
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $sheng=db("cg_market_provice")->where("isdel=0")->field("id,title")->select();
        $this->assign("sheng",$sheng);
        $kaohe2=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->order("k2.starttime")->where("k1.cid=$cid")->field("k2.id,k2.title,k2.starttime,k2.endtime,k1.id as k1id,k1.starttime as k1start,k1.endtime as k1end")->select();
        $this->assign("kaohe2",$kaohe2);
        $zong=0;
        if($sid){
            $kemu=db("cg_ti_list td")->join("cg_ti t","td.ti_id=t.id")->join("cg_jxs j","t.jxs_id=j.id")->join("cg_feiyongkemu3 k3","k3.id=td.feetype")
            ->join("cg_feiyongkemu2 k2","k2.id=k3.pid")->join("cg_feiyongkemu1 k1","k1.id=k2.pid")
            ->where("t.status=7 and (t.status2=0 or t.status2 is null) and j.provice=$sid and td.start>={$cn["starttime"]} and td.start<{$cn["endtime"]}")->group("k3.id")
            ->field("k1.title as kemu1,k2.title as kemu2,k3.title as kemu3,k3.id")->select();
            //print_r($kemu);
            for($i=0;$i<count($kemu);$i++){
                $kemu[$i]["zjine"]=0;
                for($j=0;$j<count($kaohe2);$j++){
                    $start=$kaohe2[$j]["starttime"];$end=$kaohe2[$j]["endtime"]+3600*24;
                    $jine=db("cg_ti_list td")->join("cg_ti t","td.ti_id=t.id")->join("cg_jxs j","t.jxs_id=j.id")
                    ->where("t.status=7 and (t.status2=0 or t.status2 is null) and j.provice=$sid and td.feetype={$kemu[$i]["id"]} and td.start>=$start and td.start<$end")
                    ->field("td.yjsjtrfy,td.fb_shijitourufeiyong")->select();
                    
                    $money=0;
                    foreach ($jine as $k => $v) {
                        # code...
                        if($v["fb_shijitourufeiyong"])
                            $money+=$v["fb_shijitourufeiyong"];
                        else
                            $money+=$v["yjsjtrfy"];

                    }
                    $kemu[$i]["jine"][$j]=round($money,2);
                    $kemu[$i]["zjine"]+=$kemu[$i]["jine"][$j];

                }

                $kemu[$i]["ajine"]=round($kemu[$i]["zjine"]/count($kaohe2),2);
                $zong+=$kemu[$i]["zjine"];
               
            }
            $avg=round($zong/count($kaohe2),2);
            foreach ($kemu as $k => $v) {
                # code...
                if($zong)
                    $kemu[$k]["bjine"]=round($kemu[$k]["zjine"]/$zong*100,2);
                else
                    $kemu[$k]["bjine"]=0;
            }
            $this->assign("zong",$zong);
            $this->assign("avg",$avg);
            $this->assign("kemu",$kemu);
        }
        $this->assign("sid",$sid);
        return $this->fetch();
    }
    public function shengdetdc($cid,$sid){
        $cn=db("cg_cainian")->where(["id"=>$cid])->find();
        $cn["endtime"]=$cn["endtime"]+3600*24;
        $kaohe2=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->order("k2.starttime")->where("k1.cid=$cid")->field("k2.id,k2.title,k2.starttime,k2.endtime,k1.id as k1id,k1.starttime as k1start,k1.endtime as k1end")->select();
         $zong=0;
        if($sid){
            $kemu=db("cg_ti_list td")->join("cg_ti t","td.ti_id=t.id")->join("cg_jxs j","t.jxs_id=j.id")->join("cg_feiyongkemu3 k3","k3.id=td.feetype")
            ->join("cg_feiyongkemu2 k2","k2.id=k3.pid")->join("cg_feiyongkemu1 k1","k1.id=k2.pid")
            ->where("t.status=7 and (t.status2=0 or t.status2 is null) and j.provice=$sid and td.start>={$cn["starttime"]} and td.start<{$cn["endtime"]}")->group("k3.id")
            ->field("k1.title as kemu1,k2.title as kemu2,k3.title as kemu3,k3.id")->select();
            //print_r($kemu);
            for($i=0;$i<count($kemu);$i++){
                $kemu[$i]["zjine"]=0;
                for($j=0;$j<count($kaohe2);$j++){
                    $start=$kaohe2[$j]["starttime"];$end=$kaohe2[$j]["endtime"]+3600*24;
                    $jine=db("cg_ti_list td")->join("cg_ti t","td.ti_id=t.id")->join("cg_jxs j","t.jxs_id=j.id")
                    ->where("t.status=7 and (t.status2=0 or t.status2 is null) and j.provice=$sid and td.feetype={$kemu[$i]["id"]} and td.start>=$start and td.start<$end")
                    ->field("td.yjsjtrfy,td.fb_shijitourufeiyong")->select();
                    
                    $money=0;
                    foreach ($jine as $k => $v) {
                        # code...
                        if($v["fb_shijitourufeiyong"])
                            $money+=$v["fb_shijitourufeiyong"];
                        else
                            $money+=$v["yjsjtrfy"];

                    }
                    $kemu[$i]["jine"][$j]=round($money,2);
                    $kemu[$i]["zjine"]+=$kemu[$i]["jine"][$j];

                }

                $kemu[$i]["ajine"]=round($kemu[$i]["zjine"]/count($kaohe2),2);
                $zong+=$kemu[$i]["zjine"];
               
            }
            $avg=round($zong/count($kaohe2),2);
            foreach ($kemu as $k => $v) {
                # code...
                if($zong)
                    $kemu[$k]["bjine"]=round($kemu[$k]["zjine"]/$zong*100,2);
                else
                    $kemu[$k]["bjine"]=0;
            }
        }
         $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();
        $sheng=db("cg_market_provice")->where("id=$sid")->value("title");
        Vendor("PHPExcel");


        Vendor("PHPExcel.Writer.Excel5");

        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new PHPExcel();


        $objProps = $objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("{$cn["title"]}财年{$sheng}费用科目投入");

        #################总标题

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:R1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "{$cn["title"]}财年{$sheng}费用科目投入");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
        #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:R2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "(本表单由软件导出，导出时间：" . date("Y-m-d H:i:s") . "；导出人：{$users["em_name"]})");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:R3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:R3')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:R3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:R3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"一级费用科目");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"二级费用科目");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"科目");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"1月");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"2月");
        $objPHPExcel->getActiveSheet()->setCellValue('F3',"3月");
        $objPHPExcel->getActiveSheet()->setCellValue('G3',"4月");
        $objPHPExcel->getActiveSheet()->setCellValue('H3',"5月");
        $objPHPExcel->getActiveSheet()->setCellValue('I3',"6月");
        $objPHPExcel->getActiveSheet()->setCellValue('J3',"7月");
        $objPHPExcel->getActiveSheet()->setCellValue('K3',"8月");
        $objPHPExcel->getActiveSheet()->setCellValue('L3',"9月");
        $objPHPExcel->getActiveSheet()->setCellValue('M3',"10月");
        $objPHPExcel->getActiveSheet()->setCellValue('N3',"11月");
        $objPHPExcel->getActiveSheet()->setCellValue('O3',"12月");
        $objPHPExcel->getActiveSheet()->setCellValue('P3',"小计");
        $objPHPExcel->getActiveSheet()->setCellValue('Q3',"平均");
        $objPHPExcel->getActiveSheet()->setCellValue('R3',"占比");

        $h=3;
        for($i=0;$i<count($kemu);$i++){
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$kemu[$i]["kemu1"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$kemu[$i]["kemu2"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$kemu[$i]["kemu3"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$kemu[$i]["jine"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$kemu[$i]["jine"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$kemu[$i]["jine"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$kemu[$i]["jine"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$kemu[$i]["jine"][4],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$kemu[$i]["jine"][5],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$kemu[$i]["jine"][6],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$kemu[$i]["jine"][7],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$kemu[$i]["jine"][8],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$kemu[$i]["jine"][9],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$kemu[$i]["jine"][10],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$kemu[$i]["jine"][11],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$kemu[$i]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('Q'.$h,$kemu[$i]["ajine"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('R'.$h,$kemu[$i]["bjine"].'%',PHPExcel_Cell_DataType::TYPE_STRING);
        }
        $h++;
         $objPHPExcel->getActiveSheet()->mergeCells('A'.$h.':O'.$h);
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A'.$h)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,"总计",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$zong,PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('Q'.$h,$avg,PHPExcel_Cell_DataType::TYPE_STRING);
        $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:R'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(20);
           $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(20);
           $objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(20);
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');  

    }
     //省份科目
    
    public function shengkemu($cid="",$zid=""){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        
        $cn=db("cg_cainian")->where(["id"=>$cid])->find();
      
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        
        if($zid){
            $fenqu=db("cg_market_provice")->where("id in ($zid)")->select();
            $tmp=db("cg_jxs")->where("provice in ($zid)")->field("id")->select();
            $jxs=[];
            foreach ($tmp as $key => $v) {
                # code...
                $jxs[]=$v["id"];
            }
        }else{
           $fenqu=db("cg_market_provice")->select();
            $tmp=db("cg_jxs")->field("id")->select();
            $jxs=[];
            foreach ($tmp as $key => $v) {
                # code...
                $jxs[]=$v["id"];
            }
        }
        $this->assign("fenqu",$fenqu);
        if($jxs){
            $jxs=implode(',',$jxs);
            $kemu=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_feiyongkemu3 k3","td.feetype=k3.id")
            ->join("cg_feiyongkemu2 k2","k3.pid=k2.id")->join("cg_feiyongkemu1 k1","k2.pid=k1.id")->group("td.feetype")
            ->where("t.jxs_id in ($jxs) and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$cn["starttime"]} and td.start<{$cn["endtime"]}")
            ->field("td.feetype as id,k3.title as kemu3,k2.title as kemu2,k1.title as kemu1")->select();
        }else{
            $kemu=[];
        }
        $zong=0;
        for($i=0;$i<count($kemu);$i++){
            $tmps=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->where("t.jxs_id in ($jxs) and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$cn["starttime"]} and td.start<{$cn["endtime"]} and td.feetype={$kemu[$i]["id"]}")->field("pizhunweishui,fb_shijiweishui,hx_byhx")->select();
            $jine=0;
            foreach ($tmps as $k => $v) {
                # code...
                if($v["fb_shijiweishui"]){
                    $jine+=$v["fb_shijiweishui"];
                }else{
                    $jine+=$v["pizhunweishui"];
                }
                if($v["hx_byhx"]){
                    $jine-=$v["hx_byhx"];
                }
            }

            $kemu[$i]["jine"]=round($jine,2);
            $zong+=round($jine,2);
        }
        foreach ($kemu as $k => $v) {
            # code...
            $kemu[$k]["bili"]=round($v["jine"]/$zong*100,2);
        }
        $this->assign("kemu",$kemu);
        $this->assign("zong",$zong);
        return $this->fetch();
    }
    //省销售进度
    public function shengjd($cid=''){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        
        $cn=db("cg_cainian")->where(["id"=>$cid])->find();
        $cn["endtime"]=$cn["endtime"]+3600*24;
        $this->assign("cn",$cn);
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $zarr=db("cg_market_provice")->where("isdel=0")->field("id,title")->select();
        $kaohe1=db("cg_kaoheqi1")->where(["cid"=>$cid])->select();
         for($i=0;$i<count($zarr);$i++){
            $zarr[$i]["zyjxl"]=0;$zarr[$i]["zxldc"]=0;$zarr[$i]["zdcl"]='-';
            for($j=0;$j<count($kaohe1);$j++){
                $ed=$kaohe1[$j]["endtime"]+3600*24;
                //预计销量
                $yjxl=0;
                $tmpxl=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.sheng={$zarr[$i]["id"]} and t.type_id=5 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe1[$j]["id"]}")->sum("feiyong");
                if($tmpxl){
                    $yjxl=round($tmpxl,2);
                }
                $zarr[$i]["yjxl"][$j]=$yjxl;
                $zarr[$i]["zyjxl"]+=$yjxl;
              
                //销量达成
                $xldc=0;
                $tmpxdc=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")->where("j.provice={$zarr[$i]["id"]} and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe1[$j]["starttime"]} and td.start<{$ed}")->field("totalExpectedSales,fb_shijixiaoliang")->select();
                //echo db("cg_ti")->getLastSql();exit;
                foreach ($tmpxdc as $k => $v) {
                    # code...
                    if($v["fb_shijixiaoliang"]){
                        $xldc+=$v["fb_shijixiaoliang"];
                    }else{
                        $xldc+=$v["totalExpectedSales"];
                    }
                }
                $zarr[$i]["xldc"][$j]=round($xldc,2);
                $zarr[$i]["zxldc"]+=round($xldc,2);
               

                if($zarr[$i]["yjxl"][$j]){
                    $zarr[$i]["dcl"][$j]=round($zarr[$i]["xldc"][$j]/$zarr[$i]["yjxl"][$j]*100,2);
                }else{
                    $zarr[$i]["dcl"][$j]='-';
                }
            }
         
            if($zarr[$i]["zyjxl"]){
                $zarr[$i]["zdcl"]=round($zarr[$i]["zxldc"]/$zarr[$i]["zyjxl"]*100,2);
            }
        }
        $this->assign("zarr",$zarr);
        $this->assign("kaohe1",$kaohe1);
        return $this->fetch();
    }
     public function shengjddc($cid){
       
        
        $cn=db("cg_cainian")->where(["id"=>$cid])->find();
        $cn["endtime"]=$cn["endtime"]+3600*24;
       
        $zarr=db("cg_market_provice")->where("isdel=0")->field("id,title")->select();
        $kaohe1=db("cg_kaoheqi1")->where(["cid"=>$cid])->select();
         for($i=0;$i<count($zarr);$i++){
            $zarr[$i]["zyjxl"]=0;$zarr[$i]["zxldc"]=0;$zarr[$i]["zdcl"]='-';
            for($j=0;$j<count($kaohe1);$j++){
                $ed=$kaohe1[$j]["endtime"]+3600*24;
                //预计销量
                $yjxl=0;
                $tmpxl=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.sheng={$zarr[$i]["id"]} and t.type_id=5 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe1[$j]["id"]}")->sum("feiyong");
                if($tmpxl){
                    $yjxl=round($tmpxl,2);
                }
                $zarr[$i]["yjxl"][$j]=$yjxl;
                $zarr[$i]["zyjxl"]+=$yjxl;
              
                //销量达成
                $xldc=0;
                $tmpxdc=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")->where("j.provice={$zarr[$i]["id"]} and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe1[$j]["starttime"]} and td.start<{$ed}")->field("totalExpectedSales,fb_shijixiaoliang")->select();
                //echo db("cg_ti")->getLastSql();exit;
                foreach ($tmpxdc as $k => $v) {
                    # code...
                    if($v["fb_shijixiaoliang"]){
                        $xldc+=$v["fb_shijixiaoliang"];
                    }else{
                        $xldc+=$v["totalExpectedSales"];
                    }
                }
                $zarr[$i]["xldc"][$j]=round($xldc,2);
                $zarr[$i]["zxldc"]+=round($xldc,2);
               

                if($zarr[$i]["yjxl"][$j]){
                    $zarr[$i]["dcl"][$j]=round($zarr[$i]["xldc"][$j]/$zarr[$i]["yjxl"][$j]*100,2);
                }else{
                    $zarr[$i]["dcl"][$j]='-';
                }
            }
         
            if($zarr[$i]["zyjxl"]){
                $zarr[$i]["zdcl"]=round($zarr[$i]["zxldc"]/$zarr[$i]["zyjxl"]*100,2);
            }
        }
       //excel
        $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();
        $title=$cn["title"]."财年省份目标达成报表";
        Vendor("PHPExcel");
        Vendor("PHPExcel.Writer.Excel5");
        $objPHPExcel= new PHPExcel();
        $objProps=$objPHPExcel->getProperties();
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
                
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle($title);
       
              #标题
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(20);

       

        
        $objPHPExcel->getActiveSheet()->mergeCells('A1:G1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "$title");
              //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

            #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:G2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
        #####地域信息
        $objPHPExcel->getActiveSheet()->mergeCells('A3:G3');
                 //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
         //字体
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setSize(10);

        $str="包含省份:";
        if($zarr){
            $tmparr=[];
            foreach ($zarr as $k => $v) {
                # code...
                $tmparr[]=$v["title"];
            }
            $str.=implode(',',$tmparr);
        }else{
            $str.="无";
        }
        $objPHPExcel->getActiveSheet()->setCellValue('A3',$str);

         $objPHPExcel->getActiveSheet()->getStyle('4')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A4:G4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A4:G4')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A4:G4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A4:G4')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A4',"省份名称");
        $objPHPExcel->getActiveSheet()->setCellValue('C4',"第一季度");
        $objPHPExcel->getActiveSheet()->setCellValue('D4',"第二季度");
        $objPHPExcel->getActiveSheet()->setCellValue('E4',"第三季度");
        $objPHPExcel->getActiveSheet()->setCellValue('F4',"第四季度");
        $objPHPExcel->getActiveSheet()->setCellValue('G4',"合计");
        $jarr=$zarr;
        $h=4;
        for($i=0;$i<count($jarr);$i++){
            $h++;
            $objPHPExcel->getActiveSheet()->mergeCells('A'.$h.':A'.($h+2));
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$jarr[$i]["title"],PHPExcel_Cell_DataType::TYPE_STRING);
             //垂直剧中
            $objPHPExcel->getActiveSheet()->getStyle('A'.$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"销售目标(元)",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$jarr[$i]["yjxl"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$jarr[$i]["yjxl"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$jarr[$i]["yjxl"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$jarr[$i]["yjxl"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$jarr[$i]["zyjxl"],PHPExcel_Cell_DataType::TYPE_STRING);
           
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"实际达成(元)",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$jarr[$i]["xldc"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$jarr[$i]["xldc"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$jarr[$i]["xldc"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$jarr[$i]["xldc"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$jarr[$i]["zxldc"],PHPExcel_Cell_DataType::TYPE_STRING);
          
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"实际达成率",PHPExcel_Cell_DataType::TYPE_STRING);
            if($jarr[$i]["dcl"][0])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$jarr[$i]["dcl"][0].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
           if($jarr[$i]["dcl"][1])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$jarr[$i]["dcl"][1].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
            if($jarr[$i]["dcl"][2])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$jarr[$i]["dcl"][2].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
            if($jarr[$i]["dcl"][3])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$jarr[$i]["dcl"][3].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
            if($jarr[$i]["zdcl"])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$jarr[$i]["zdcl"].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
            
          
        }
           $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:G'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }
    //省发运排名
    public function shengfy($cid=''){
        $request = Request::instance();
        $data = $request->param();
        $where="1=1 ";
        if($data){
            if(trim($data["fenqu"])){
                $fenqu=trim($data["fenqu"]);
                $where.="and z.title='{$fenqu}' ";
            }
            if(trim($data["provice"])){
                $provice=trim($data["provice"]);
                $where.="and p.title='{$provice}' ";
            }
        }else{
            $data=["fenqu"=>"","provice"=>""];
        }
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        
        $cn=db("cg_cainian")->where(["id"=>$cid])->find();
        $cst=date("Y-m-d",$cn["starttime"]);
        $ced=date("Y-m-d",$cn["endtime"]);
        $this->assign("cn",$cn);
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $kaohe2=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->order("k2.starttime")->where("k1.cid=$cid")->field("k2.id,k2.title,k2.starttime,k2.endtime,k1.id as k1id,k1.starttime as k1start,k1.endtime as k1end")->select();
        $apply=db("bx_fy f")->join("cg_jxs j","f.sdAgency_id=j.id")->join("cg_market_provice p","j.provice=p.id")->join("cg_market_zoning z","p.zoning=z.id")
        ->where("f.fyDate>='{$cst}' and f.fyDate<='{$ced}' and $where")
        ->group("p.id")->order("sum(fyZheMoney) desc")->field("p.id,p.title as sheng,sum(fyZheMoney) as zong,z.title as fenqu")->select();
       // print_r($apply);
       $zong=0;
       for($i=0;$i<count($apply);$i++){
            for($j=0;$j<count($kaohe2);$j++){
                $st=date("Y-m-d",$kaohe2[$j]["starttime"]);
                $ed=date("Y-m-d",$kaohe2[$j]["endtime"]);
                $jine=db("bx_fy f")->join("cg_jxs j","f.sdAgency_id=j.id")->join("cg_market_provice p","j.provice=p.id")->join("cg_market_zoning z","p.zoning=z.id")
                ->where("p.id={$apply[$i]["id"]} and f.fyDate>='{$st}' and f.fyDate<='{$ed}'")->sum("fyZheMoney");
                if($jine){
                    $apply[$i]["jine"][$j]=round($jine,2);
                }else{
                    $apply[$i]["jine"][$j]=0;
                }
                if(isset($kaohe2[$j]["zjine"]))
                    $kaohe2[$j]["zjine"]+=$apply[$i]["jine"][$j];
                else
                    $kaohe2[$j]["zjine"]=$apply[$i]["jine"][$j];
            }
            $zong+=$apply[$i]["zong"];
       }
       $this->assign("zong",$zong);
       $this->assign("apply",$apply);
       $this->assign("kaohe2",$kaohe2);
       $this->assign("data",$data);
        return $this->fetch();
    }
    public function shengfydc($cid){
        $cn=db("cg_cainian")->where(["id"=>$cid])->find();
        $cst=date("Y-m-d",$cn["starttime"]);
        $ced=date("Y-m-d",$cn["endtime"]);
        $where="1=1 ";
        $kaohe2=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->order("k2.starttime")->where("k1.cid=$cid")->field("k2.id,k2.title,k2.starttime,k2.endtime,k1.id as k1id,k1.starttime as k1start,k1.endtime as k1end")->select();
        $apply=db("bx_fy f")->join("cg_jxs j","f.sdAgency_id=j.id")->join("cg_market_provice p","j.provice=p.id")->join("cg_market_zoning z","p.zoning=z.id")
        ->where("f.fyDate>='{$cst}' and f.fyDate<='{$ced}' and $where")
        ->group("p.id")->order("sum(fyZheMoney) desc")->field("p.id,p.title as sheng,sum(fyZheMoney) as zong,z.title as fenqu")->select();
       // print_r($apply);
       $zong=0;
       for($i=0;$i<count($apply);$i++){
            for($j=0;$j<count($kaohe2);$j++){
                $st=date("Y-m-d",$kaohe2[$j]["starttime"]);
                $ed=date("Y-m-d",$kaohe2[$j]["endtime"]);
                $jine=db("bx_fy f")->join("cg_jxs j","f.sdAgency_id=j.id")->join("cg_market_provice p","j.provice=p.id")->join("cg_market_zoning z","p.zoning=z.id")
                ->where("p.id={$apply[$i]["id"]} and f.fyDate>='{$st}' and f.fyDate<='{$ed}'")->sum("fyZheMoney");
                if($jine){
                    $apply[$i]["jine"][$j]=round($jine,2);
                }else{
                    $apply[$i]["jine"][$j]=0;
                }
                if(isset($kaohe2[$j]["zjine"]))
                    $kaohe2[$j]["zjine"]+=$apply[$i]["jine"][$j];
                else
                    $kaohe2[$j]["zjine"]=$apply[$i]["jine"][$j];
            }
            $zong+=$apply[$i]["zong"];
       }
        $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();

        Vendor("PHPExcel");


        Vendor("PHPExcel.Writer.Excel5");

        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new PHPExcel();


        $objProps = $objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("省份发运排名");

        #################总标题

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:P1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "省份发运排名");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
        #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:P2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "(本表单由软件导出，导出时间：" . date("Y-m-d H:i:s") . "；导出人：{$users["em_name"]})");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"序号");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"分区");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"省份");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"1月");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"2月");
        $objPHPExcel->getActiveSheet()->setCellValue('F3',"3月");
        $objPHPExcel->getActiveSheet()->setCellValue('G3',"4月");
        $objPHPExcel->getActiveSheet()->setCellValue('H3',"5月");
        $objPHPExcel->getActiveSheet()->setCellValue('I3',"6月");
        $objPHPExcel->getActiveSheet()->setCellValue('J3',"7月");
        $objPHPExcel->getActiveSheet()->setCellValue('K3',"8月");
        $objPHPExcel->getActiveSheet()->setCellValue('L3',"9月");
        $objPHPExcel->getActiveSheet()->setCellValue('M3',"10月");
        $objPHPExcel->getActiveSheet()->setCellValue('N3',"11月");
        $objPHPExcel->getActiveSheet()->setCellValue('O3',"12月");
        $objPHPExcel->getActiveSheet()->setCellValue('P3',"小计");

        $h=3;
        for($i=0;$i<count($apply);$i++){
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$i+1,PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$apply[$i]["fenqu"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$apply[$i]["sheng"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$apply[$i]["jine"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$apply[$i]["jine"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$apply[$i]["jine"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$apply[$i]["jine"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$apply[$i]["jine"][4],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$apply[$i]["jine"][5],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$apply[$i]["jine"][6],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$apply[$i]["jine"][7],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$apply[$i]["jine"][8],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$apply[$i]["jine"][9],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$apply[$i]["jine"][10],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$apply[$i]["jine"][11],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('p'.$h,$apply[$i]["zong"],PHPExcel_Cell_DataType::TYPE_STRING);
        }
         $h++;
         $objPHPExcel->getActiveSheet()->mergeCells('A'.$h.':C'.$h);
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A'.$h)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,"总计",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$kaohe2[0]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$kaohe2[1]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$kaohe2[2]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$kaohe2[3]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$kaohe2[4]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$kaohe2[5]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$kaohe2[6]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$kaohe2[7]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$kaohe2[8]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$kaohe2[9]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$kaohe2[10]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$kaohe2[11]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$zong,PHPExcel_Cell_DataType::TYPE_STRING);
        $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:P'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(20);
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');  

    }
    //各省费用率
    public function shengfyl($cid=''){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $nian=db("cg_cainian")->where(["id"=>$cid])->value("title");
        $this->assign("nian",$nian);
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $kaohe1=db("cg_kaoheqi1")->where(["cid"=>$cid])->select();

        $this->assign("kaohe1",$kaohe1);
         $where="isdel=0 ";
        $zarr=db("cg_market_provice")->where("isdel=0")->field("id,title")->select();
          for($i=0;$i<count($zarr);$i++){
            $zarr[$i]["zyjxl"]=0;$zarr[$i]["zyjfy"]=0;$zarr[$i]["zxldc"]=0;$zarr[$i]["zfysq"]=0;$zarr[$i]["zyjfyl"]=0;
            $zarr[$i]["zsjfyl"]=0;$zarr[$i]["zdcl"]='-';
            for($j=0;$j<count($kaohe1);$j++){
                $ed=$kaohe1[$j]["endtime"]+3600*24;
                //预计销量
                $yjxl=0;
                $tmpxl=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.sheng={$zarr[$i]["id"]} and t.type_id=5 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe1[$j]["id"]}")->sum("feiyong");
                if($tmpxl){
                    $yjxl=round($tmpxl,2);
                }
                $zarr[$i]["yjxl"][$j]=$yjxl;
                $zarr[$i]["zyjxl"]+=$yjxl;
                //预计费用预算
                $yjfy=0;
                $tmpfy=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.sheng={$zarr[$i]["id"]} and t.type_id=2 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe1[$j]["id"]}")->sum("feiyong");
                if($tmpfy){
                    $yjfy=round($tmpfy,2);
                }
                $zarr[$i]["yjfy"][$j]=$yjfy;
                $zarr[$i]["zyjfy"]+=$yjfy;
                //销量达成
                $xldc=0;
                $tmpxdc=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")->where("j.provice={$zarr[$i]["id"]} and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe1[$j]["starttime"]} and td.start<{$ed}")->field("totalExpectedSales,fb_shijixiaoliang")->select();
                //echo db("cg_ti")->getLastSql();exit;
                foreach ($tmpxdc as $k => $v) {
                    # code...
                    if($v["fb_shijixiaoliang"]){
                        $xldc+=$v["fb_shijixiaoliang"];
                    }else{
                        $xldc+=$v["totalExpectedSales"];
                    }
                }
                $zarr[$i]["xldc"][$j]=round($xldc,2);
                $zarr[$i]["zxldc"]+=round($xldc,2);
                //费用申请
                $fysq=0;
                $tmpsq=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")->where("j.provice={$zarr[$i]["id"]} and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe1[$j]["starttime"]} and td.start<{$ed}")->field("yjsjtrfy,fb_shijitourufeiyong")->select();
                foreach ($tmpsq as $k => $v) {
                    # code...
                    if($v["fb_shijitourufeiyong"]){
                        $fysq+=$v["fb_shijitourufeiyong"];
                    }else{
                        $fysq+=$v["yjsjtrfy"];
                    }
                }
                $zarr[$i]["fysq"][$j]=round($fysq,2);
                $zarr[$i]["zfysq"]+=round($fysq,2);
                if($zarr[$i]["yjxl"][$j]){
                    $zarr[$i]["yjfyl"][$j]=round($zarr[$i]["yjfy"][$j]/$zarr[$i]["yjxl"][$j]*100,2);
                }else{
                    $zarr[$i]["yjfyl"][$j]=0;
                }

                if($zarr[$i]["xldc"][$j]){
                    $zarr[$i]["sjfyl"][$j]=round($zarr[$i]["fysq"][$j]/$zarr[$i]["xldc"][$j]*100,2);
                }else{
                    $zarr[$i]["sjfyl"][$j]=0;
                }
                $zarr[$i]["cy"][$j]=$zarr[$i]["sjfyl"][$j]-$zarr[$i]["yjfyl"][$j];
                
            }
            if($zarr[$i]["zyjxl"]){
                $zarr[$i]["zyjfyl"]=round($zarr[$i]["zyjfy"]/$zarr[$i]["zyjxl"]*100,2);
            }
            if($zarr[$i]["zxldc"]){
                $zarr[$i]["zsjfyl"]=round($zarr[$i]["zfysq"]/$zarr[$i]["zxldc"]*100,2);
            }
            $zarr[$i]["zcy"]=$zarr[$i]["zsjfyl"]-$zarr[$i]["zyjfyl"];
        }
        $this->assign("zarr",$zarr);

        return $this->fetch();
    }
    public function shengfyldc($cid){
         $nian=db("cg_cainian")->where(["id"=>$cid])->value("title");
       
        $kaohe1=db("cg_kaoheqi1")->where(["cid"=>$cid])->select();

       
         $where="isdel=0 ";
        $zarr=db("cg_market_provice")->where("isdel=0")->field("id,title")->select();
          for($i=0;$i<count($zarr);$i++){
            $zarr[$i]["zyjxl"]=0;$zarr[$i]["zyjfy"]=0;$zarr[$i]["zxldc"]=0;$zarr[$i]["zfysq"]=0;$zarr[$i]["zyjfyl"]=0;
            $zarr[$i]["zsjfyl"]=0;$zarr[$i]["zdcl"]='-';
            for($j=0;$j<count($kaohe1);$j++){
                $ed=$kaohe1[$j]["endtime"]+3600*24;
                //预计销量
                $yjxl=0;
                $tmpxl=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.sheng={$zarr[$i]["id"]} and t.type_id=5 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe1[$j]["id"]}")->sum("feiyong");
                if($tmpxl){
                    $yjxl=round($tmpxl,2);
                }
                $zarr[$i]["yjxl"][$j]=$yjxl;
                $zarr[$i]["zyjxl"]+=$yjxl;
                //预计费用预算
                $yjfy=0;
                $tmpfy=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.sheng={$zarr[$i]["id"]} and t.type_id=2 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe1[$j]["id"]}")->sum("feiyong");
                if($tmpfy){
                    $yjfy=round($tmpfy,2);
                }
                $zarr[$i]["yjfy"][$j]=$yjfy;
                $zarr[$i]["zyjfy"]+=$yjfy;
                //销量达成
                $xldc=0;
                $tmpxdc=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")->where("j.provice={$zarr[$i]["id"]} and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe1[$j]["starttime"]} and td.start<{$ed}")->field("totalExpectedSales,fb_shijixiaoliang")->select();
                //echo db("cg_ti")->getLastSql();exit;
                foreach ($tmpxdc as $k => $v) {
                    # code...
                    if($v["fb_shijixiaoliang"]){
                        $xldc+=$v["fb_shijixiaoliang"];
                    }else{
                        $xldc+=$v["totalExpectedSales"];
                    }
                }
                $zarr[$i]["xldc"][$j]=round($xldc,2);
                $zarr[$i]["zxldc"]+=round($xldc,2);
                //费用申请
                $fysq=0;
                $tmpsq=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")->where("j.provice={$zarr[$i]["id"]} and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe1[$j]["starttime"]} and td.start<{$ed}")->field("yjsjtrfy,fb_shijitourufeiyong")->select();
                foreach ($tmpsq as $k => $v) {
                    # code...
                    if($v["fb_shijitourufeiyong"]){
                        $fysq+=$v["fb_shijitourufeiyong"];
                    }else{
                        $fysq+=$v["yjsjtrfy"];
                    }
                }
                $zarr[$i]["fysq"][$j]=round($fysq,2);
                $zarr[$i]["zfysq"]+=round($fysq,2);
                if($zarr[$i]["yjxl"][$j]){
                    $zarr[$i]["yjfyl"][$j]=round($zarr[$i]["yjfy"][$j]/$zarr[$i]["yjxl"][$j]*100,2);
                }else{
                    $zarr[$i]["yjfyl"][$j]=0;
                }

                if($zarr[$i]["xldc"][$j]){
                    $zarr[$i]["sjfyl"][$j]=round($zarr[$i]["fysq"][$j]/$zarr[$i]["xldc"][$j]*100,2);
                }else{
                    $zarr[$i]["sjfyl"][$j]=0;
                }
                $zarr[$i]["cy"][$j]=$zarr[$i]["sjfyl"][$j]-$zarr[$i]["yjfyl"][$j];
                
            }
            if($zarr[$i]["zyjxl"]){
                $zarr[$i]["zyjfyl"]=round($zarr[$i]["zyjfy"]/$zarr[$i]["zyjxl"]*100,2);
            }
            if($zarr[$i]["zxldc"]){
                $zarr[$i]["zsjfyl"]=round($zarr[$i]["zfysq"]/$zarr[$i]["zxldc"]*100,2);
            }
            $zarr[$i]["zcy"]=$zarr[$i]["zsjfyl"]-$zarr[$i]["zyjfyl"];
        }
        //导出
        ////excel
        $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();
        $title=$nian."财年各省份费用率情况";
        Vendor("PHPExcel");
        Vendor("PHPExcel.Writer.Excel5");
        $objPHPExcel= new PHPExcel();
        $objProps=$objPHPExcel->getProperties();
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
                
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle($title);
       
              #标题
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(20);

       

        
        $objPHPExcel->getActiveSheet()->mergeCells('A1:F1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "$title");
              //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

            #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:F2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
        #####地域信息
        $objPHPExcel->getActiveSheet()->mergeCells('A3:F3');
                 //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
         //字体
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setSize(10);

        $str="包含省份:";
        if($zarr){
            $tmparr=[];
            foreach ($zarr as $k => $v) {
                # code...
                $tmparr[]=$v["title"];
            }
            $str.=implode(',',$tmparr);
        }else{
            $str.="无";
        }
        $objPHPExcel->getActiveSheet()->setCellValue('A3',$str);

         $objPHPExcel->getActiveSheet()->getStyle('4')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A4:F4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A4:F4')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A4:F4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A4:F4')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A4',"省份名称");
        $objPHPExcel->getActiveSheet()->setCellValue('C4',"第一季度");
        $objPHPExcel->getActiveSheet()->setCellValue('D4',"第二季度");
        $objPHPExcel->getActiveSheet()->setCellValue('E4',"第三季度");
        $objPHPExcel->getActiveSheet()->setCellValue('F4',"第四季度");
        
        $jarr=$zarr;
        $h=4;
        for($i=0;$i<count($jarr);$i++){
            $h++;
            $objPHPExcel->getActiveSheet()->mergeCells('A'.$h.':A'.($h+2));
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$jarr[$i]["title"],PHPExcel_Cell_DataType::TYPE_STRING);
             //垂直剧中
            $objPHPExcel->getActiveSheet()->getStyle('A'.$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
           
            
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"预算费用率",PHPExcel_Cell_DataType::TYPE_STRING);
            if($jarr[$i]["yjfyl"][0])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$jarr[$i]["yjfyl"][0].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
           if($jarr[$i]["yjfyl"][1])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$jarr[$i]["yjfyl"][1].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
            if($jarr[$i]["yjfyl"][2])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$jarr[$i]["yjfyl"][2].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
            if($jarr[$i]["yjfyl"][3])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$jarr[$i]["yjfyl"][3].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
            
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"实际费用率",PHPExcel_Cell_DataType::TYPE_STRING);
            if($jarr[$i]["sjfyl"][0])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$jarr[$i]["sjfyl"][0].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
           if($jarr[$i]["sjfyl"][1])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$jarr[$i]["sjfyl"][1].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
            if($jarr[$i]["sjfyl"][2])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$jarr[$i]["sjfyl"][2].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
            if($jarr[$i]["sjfyl"][3])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$jarr[$i]["sjfyl"][3].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
            
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"差异",PHPExcel_Cell_DataType::TYPE_STRING);
            if($jarr[$i]["cy"][0])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$jarr[$i]["cy"][0].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
           if($jarr[$i]["cy"][1])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$jarr[$i]["cy"][1].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
            if($jarr[$i]["cy"][2])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$jarr[$i]["cy"][2].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
            if($jarr[$i]["cy"][3])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$jarr[$i]["cy"][3].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
        }
           $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:F'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }
    //各省销售营运报表
    public function shengyy($cid='',$sid=''){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        
        $cn=db("cg_cainian")->where(["id"=>$cid])->find();
        $cn["endtime"]=$cn["endtime"]+3600*24;
        $this->assign("cn",$cn);
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $sheng=db("cg_market_provice")->where("isdel=0")->field("id,title")->select();
        $this->assign("sheng",$sheng);
        $kaohe2=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->order("k2.starttime")->where("k1.cid=$cid")->field("k2.id,k2.title,k2.starttime,k2.endtime,k1.id as k1id,k1.starttime as k1start,k1.endtime as k1end")->select();
        $this->assign("kaohe2",$kaohe2);
        $this->assign("sid",$sid);
        if($sid){
            $zarr=db("cg_market_provice")->where(["id"=>$sid])->field("id,title")->find();
            $zarr["zyjxl"]=0;$zarr["zyjfy"]=0;$zarr["zxldc"]=0;$zarr["zfysq"]=0;$zarr["zyjfyl"]=0;
            $zarr["zsjfyl"]=0;
            for($j=0;$j<count($kaohe2);$j++){
                $ed=$kaohe2[$j]["endtime"]+3600*24;
                $k1ts=($kaohe2[$j]["k1end"]-$kaohe2[$j]["k1start"])/3600/24+1;
                $k2ts=($kaohe2[$j]["endtime"]-$kaohe2[$j]["starttime"])/3600/24+1;
                //预计销量
                $yjxl=0;
                $tmpxl=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.sheng={$zarr["id"]} and t.type_id=5 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.erjikaoheqi={$kaohe2[$j]["id"]}")->sum("feiyong");
                if($tmpxl){
                    $yjxl+=round($tmpxl,2);
                }
                $tmpxl2=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.sheng={$zarr["id"]} and t.type_id=5 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe2[$j]["k1id"]} and td.erjikaoheqi is null")->sum("feiyong");
                if($tmpxl2){
                    $yjxl+=round($tmpxl2/$k1ts*$k2ts,2);
                }
                $zarr["yjxl"][$j]=$yjxl;
                $zarr["zyjxl"]+=$yjxl;
                //预计费用预算
                $yjfy=0;
                $tmpfy=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.sheng={$zarr["id"]} and t.type_id=2 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.erjikaoheqi={$kaohe2[$j]["id"]}")->sum("feiyong");
                if($tmpfy){
                    $yjfy=round($tmpfy,2);
                }
                $tmpfy1=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.sheng={$zarr["id"]} and t.type_id=2 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe2[$j]["k1id"]} and td.erjikaoheqi is null")->sum("feiyong");
                if($tmpfy1){
                    $yjfy+=round($tmpfy1/$k1ts*$k2ts,2);
                }
                $zarr["yjfy"][$j]=$yjfy;
                $zarr["zyjfy"]+=$yjfy;
                //销量达成
                $xldc=0;
                $tmpxdc=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")->where("j.provice={$zarr["id"]} and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe2[$j]["starttime"]} and td.start<{$ed}")->field("totalExpectedSales,fb_shijixiaoliang")->select();
                foreach ($tmpxdc as $k => $v) {
                    # code...
                    if($v["fb_shijixiaoliang"]){
                        $xldc+=$v["fb_shijixiaoliang"];
                    }else{
                        $xldc+=$v["totalExpectedSales"];
                    }
                }
                $zarr["xldc"][$j]=round($xldc,2);
                $zarr["zxldc"]+=round($xldc,2);
                //费用申请
                $fysq=0;
                $tmpsq=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")->where("j.provice={$zarr["id"]} and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe2[$j]["starttime"]} and td.start<$ed")->field("yjsjtrfy,fb_shijitourufeiyong")->select();
                foreach ($tmpsq as $k => $v) {
                    # code...
                    if($v["fb_shijitourufeiyong"]){
                        $fysq+=$v["fb_shijitourufeiyong"];
                    }else{
                        $fysq+=$v["yjsjtrfy"];
                    }
                }
                $zarr["fysq"][$j]=round($fysq,2);
                $zarr["zfysq"]+=round($fysq,2);
                if($zarr["yjxl"][$j]){
                    $zarr["yjfyl"][$j]=round($zarr["yjfy"][$j]/$zarr["yjxl"][$j]*100,2);
                }else{
                    $zarr["yjfyl"][$j]=0;
                }

                if($zarr["xldc"][$j]){
                    $zarr["sjfyl"][$j]=round($zarr["fysq"][$j]/$zarr["xldc"][$j]*100,2);
                }else{
                    $zarr["sjfyl"][$j]=0;
                }

               
            }
            if($zarr["zyjxl"]){
                $zarr["zyjfyl"]=round($zarr["zyjfy"]/$zarr["zyjxl"]*100,2);
            }
            if($zarr["zxldc"]){
                $zarr["zsjfyl"]=round($zarr["zfysq"]/$zarr["zxldc"]*100,2);
            }

            if(count($kaohe2)){
                $zarr["ayjxl"]=round($zarr["zyjxl"]/count($kaohe2),2);
                $zarr["ayjfy"]=round($zarr["zyjfy"]/count($kaohe2),2);
                $zarr["axldc"]=round($zarr["zxldc"]/count($kaohe2),2);
                $zarr["afysq"]=round($zarr["zfysq"]/count($kaohe2),2);
            }else{
                $zarr["ayjxl"]=0;
                $zarr["ayjfy"]=0;
                $zarr["axldc"]=0;
                $zarr["afysq"]=0;
            }
          $this->assign("zarr",$zarr);
        }
        
        return $this->fetch();
    }
    public function shengyydc($sid,$cid){
        $cn=db("cg_cainian")->where(["id"=>$cid])->find();
        $cn["endtime"]=$cn["endtime"]+3600*24;
      
        $sheng=db("cg_market_provice")->where("isdel=0")->field("id,title")->select();
       
        $kaohe2=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->order("k2.starttime")->where("k1.cid=$cid")->field("k2.id,k2.title,k2.starttime,k2.endtime,k1.id as k1id,k1.starttime as k1start,k1.endtime as k1end")->select();
      
        if($sid){
            $zarr=db("cg_market_provice")->where(["id"=>$sid])->field("id,title")->find();
            $zarr["zyjxl"]=0;$zarr["zyjfy"]=0;$zarr["zxldc"]=0;$zarr["zfysq"]=0;$zarr["zyjfyl"]=0;
            $zarr["zsjfyl"]=0;
            for($j=0;$j<count($kaohe2);$j++){
                $ed=$kaohe2[$j]["endtime"]+3600*24;
                $k1ts=($kaohe2[$j]["k1end"]-$kaohe2[$j]["k1start"])/3600/24+1;
                $k2ts=($kaohe2[$j]["endtime"]-$kaohe2[$j]["starttime"])/3600/24+1;
                //预计销量
                $yjxl=0;
                $tmpxl=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.sheng={$zarr["id"]} and t.type_id=5 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.erjikaoheqi={$kaohe2[$j]["id"]}")->sum("feiyong");
                if($tmpxl){
                    $yjxl+=round($tmpxl,2);
                }
                $tmpxl2=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.sheng={$zarr["id"]} and t.type_id=5 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe2[$j]["k1id"]} and td.erjikaoheqi is null")->sum("feiyong");
                if($tmpxl2){
                    $yjxl+=round($tmpxl2/$k1ts*$k2ts,2);
                }
                $zarr["yjxl"][$j]=$yjxl;
                $zarr["zyjxl"]+=$yjxl;
                //预计费用预算
                $yjfy=0;
                $tmpfy=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.sheng={$zarr["id"]} and t.type_id=2 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.erjikaoheqi={$kaohe2[$j]["id"]}")->sum("feiyong");
                if($tmpfy){
                    $yjfy=round($tmpfy,2);
                }
                $tmpfy1=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.sheng={$zarr["id"]} and t.type_id=2 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe2[$j]["k1id"]} and td.erjikaoheqi is null")->sum("feiyong");
                if($tmpfy1){
                    $yjfy+=round($tmpfy1/$k1ts*$k2ts,2);
                }
                $zarr["yjfy"][$j]=$yjfy;
                $zarr["zyjfy"]+=$yjfy;
                //销量达成
                $xldc=0;
                $tmpxdc=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")->where("j.provice={$zarr["id"]} and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe2[$j]["starttime"]} and td.start<{$ed}")->field("totalExpectedSales,fb_shijixiaoliang")->select();
                foreach ($tmpxdc as $k => $v) {
                    # code...
                    if($v["fb_shijixiaoliang"]){
                        $xldc+=$v["fb_shijixiaoliang"];
                    }else{
                        $xldc+=$v["totalExpectedSales"];
                    }
                }
                $zarr["xldc"][$j]=round($xldc,2);
                $zarr["zxldc"]+=round($xldc,2);
                //费用申请
                $fysq=0;
                $tmpsq=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")->where("j.provice={$zarr["id"]} and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe2[$j]["starttime"]} and td.start<$ed")->field("yjsjtrfy,fb_shijitourufeiyong")->select();
                foreach ($tmpsq as $k => $v) {
                    # code...
                    if($v["fb_shijitourufeiyong"]){
                        $fysq+=$v["fb_shijitourufeiyong"];
                    }else{
                        $fysq+=$v["yjsjtrfy"];
                    }
                }
                $zarr["fysq"][$j]=round($fysq,2);
                $zarr["zfysq"]+=round($fysq,2);
                if($zarr["yjxl"][$j]){
                    $zarr["yjfyl"][$j]=round($zarr["yjfy"][$j]/$zarr["yjxl"][$j]*100,2);
                }else{
                    $zarr["yjfyl"][$j]=0;
                }

                if($zarr["xldc"][$j]){
                    $zarr["sjfyl"][$j]=round($zarr["fysq"][$j]/$zarr["xldc"][$j]*100,2);
                }else{
                    $zarr["sjfyl"][$j]=0;
                }

               
            }
            if($zarr["zyjxl"]){
                $zarr["zyjfyl"]=round($zarr["zyjfy"]/$zarr["zyjxl"]*100,2);
            }
            if($zarr["zxldc"]){
                $zarr["zsjfyl"]=round($zarr["zfysq"]/$zarr["zxldc"]*100,2);
            }

            if(count($kaohe2)){
                $zarr["ayjxl"]=round($zarr["zyjxl"]/count($kaohe2),2);
                $zarr["ayjfy"]=round($zarr["zyjfy"]/count($kaohe2),2);
                $zarr["axldc"]=round($zarr["zxldc"]/count($kaohe2),2);
                $zarr["afysq"]=round($zarr["zfysq"]/count($kaohe2),2);
            }else{
                $zarr["ayjxl"]=0;
                $zarr["ayjfy"]=0;
                $zarr["axldc"]=0;
                $zarr["afysq"]=0;
            }
          
        }
         $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();
        $title=$cn["title"]."财年省份销售营运报表";
        Vendor("PHPExcel");
        Vendor("PHPExcel.Writer.Excel5");
        $objPHPExcel= new PHPExcel();
        $objProps=$objPHPExcel->getProperties();
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
                
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle($title);
       
              #标题
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(20);

       

        
        $objPHPExcel->getActiveSheet()->mergeCells('A1:P1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "$title");
              //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

            #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:P2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
     
     

         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"省份名称");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"项目");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"1月");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"2月");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"3月");
        $objPHPExcel->getActiveSheet()->setCellValue('F3',"4月");
        $objPHPExcel->getActiveSheet()->setCellValue('G3',"5月");
        $objPHPExcel->getActiveSheet()->setCellValue('H3',"6月");
        $objPHPExcel->getActiveSheet()->setCellValue('I3',"7月");
        $objPHPExcel->getActiveSheet()->setCellValue('J3',"8月");
        $objPHPExcel->getActiveSheet()->setCellValue('K3',"9月");
        $objPHPExcel->getActiveSheet()->setCellValue('L3',"10月");
        $objPHPExcel->getActiveSheet()->setCellValue('M3',"11月");
        $objPHPExcel->getActiveSheet()->setCellValue('N3',"12月");
        $objPHPExcel->getActiveSheet()->setCellValue('O3',"小计");
        $objPHPExcel->getActiveSheet()->setCellValue('P3',"平均");

        $h=4;
        $objPHPExcel->getActiveSheet()->mergeCells('A4:A9');

             //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A'.$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$zarr["title"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"预计销售目标(元)",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$zarr["yjxl"][0],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$zarr["yjxl"][1],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$zarr["yjxl"][2],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$zarr["yjxl"][3],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$zarr["yjxl"][4],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$zarr["yjxl"][5],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$zarr["yjxl"][6],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$zarr["yjxl"][7],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$zarr["yjxl"][8],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$zarr["yjxl"][9],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$zarr["yjxl"][10],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$zarr["yjxl"][11],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$zarr["zyjxl"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$zarr["ayjxl"],PHPExcel_Cell_DataType::TYPE_STRING);
        $h++;
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"预计费用预算(元)",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$zarr["yjfy"][0],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$zarr["yjfy"][1],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$zarr["yjfy"][2],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$zarr["yjfy"][3],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$zarr["yjfy"][4],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$zarr["yjfy"][5],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$zarr["yjfy"][6],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$zarr["yjfy"][7],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$zarr["yjfy"][8],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$zarr["yjfy"][9],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$zarr["yjfy"][10],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$zarr["yjfy"][11],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$zarr["zyjfy"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$zarr["ayjfy"],PHPExcel_Cell_DataType::TYPE_STRING);
        $h++;
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"实际销售达成(元)",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$zarr["xldc"][0],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$zarr["xldc"][1],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$zarr["xldc"][2],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$zarr["xldc"][3],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$zarr["xldc"][4],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$zarr["xldc"][5],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$zarr["xldc"][6],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$zarr["xldc"][7],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$zarr["xldc"][8],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$zarr["xldc"][9],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$zarr["xldc"][10],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$zarr["xldc"][11],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$zarr["zxldc"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$zarr["axldc"],PHPExcel_Cell_DataType::TYPE_STRING);
        $h++;
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"实际费用申请(元)",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$zarr["fysq"][0],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$zarr["fysq"][1],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$zarr["fysq"][2],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$zarr["fysq"][3],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$zarr["fysq"][4],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$zarr["fysq"][5],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$zarr["fysq"][6],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$zarr["fysq"][7],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$zarr["fysq"][8],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$zarr["fysq"][9],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$zarr["fysq"][10],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$zarr["fysq"][11],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$zarr["zfysq"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$zarr["afysq"],PHPExcel_Cell_DataType::TYPE_STRING);
        $h++;
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"预计费用率",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$zarr["yjfyl"][0]."%",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$zarr["yjfyl"][1]."%",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$zarr["yjfyl"][2]."%",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$zarr["yjfyl"][3]."%",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$zarr["yjfyl"][4]."%",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$zarr["yjfyl"][5]."%",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$zarr["yjfyl"][6]."%",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$zarr["yjfyl"][7]."%",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$zarr["yjfyl"][8]."%",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$zarr["yjfyl"][9]."%",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$zarr["yjfyl"][10]."%",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$zarr["yjfyl"][11]."%",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$zarr["zyjfyl"].'%',PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$zarr["zyjfyl"].'%',PHPExcel_Cell_DataType::TYPE_STRING);
         $h++;
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"实际费用率",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$zarr["sjfyl"][0]."%",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$zarr["sjfyl"][1]."%",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$zarr["sjfyl"][2]."%",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$zarr["sjfyl"][3]."%",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$zarr["sjfyl"][4]."%",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$zarr["sjfyl"][5]."%",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$zarr["sjfyl"][6]."%",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$zarr["sjfyl"][7]."%",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$zarr["sjfyl"][8]."%",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$zarr["sjfyl"][9]."%",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$zarr["sjfyl"][10]."%",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$zarr["sjfyl"][11]."%",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$zarr["zsjfyl"].'%',PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$zarr["zsjfyl"].'%',PHPExcel_Cell_DataType::TYPE_STRING);

         $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:P'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
             $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(20);
           
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');  
    }
    //省客户
    public function shengkh(){
        $fenqu=Db::table("cg_market_provice")->where(["isdel"=>0])->field("id,title")->select();
        for($i=0;$i<count($fenqu);$i++){
            $fenqu[$i]["importjxs"]=db("cg_jxs")->where("isdel=0 and provice={$fenqu[$i]["id"]} and import=1")->count();
            $fenqu[$i]["alljxs"]=db("cg_jxs")->where("isdel=0 and provice={$fenqu[$i]["id"]}")->count();
            $fenqu[$i]["otherjxs"]=db("cg_jxs")->where("isdel=0 and provice={$fenqu[$i]["id"]} and import=0")->count();
            $fenqu[$i]["system"]=db("cg_system s")->join("cg_jxs j","s.jxs=j.id")->where("s.isdel=0 and j.isdel=0 and j.provice={$fenqu[$i]["id"]}")->count();
            $fenqu[$i]["allnet"]=db("cg_system s")->join("cg_jxs j","s.jxs=j.id")->join("cg_net n","n.system_id=s.id")->where("s.isdel=0 and j.isdel=0 and n.isdel=0 and j.provice={$fenqu[$i]["id"]}")->count();
            $fenqu[$i]["keynet"]=db("cg_system s")->join("cg_jxs j","s.jxs=j.id")->join("cg_net n","n.system_id=s.id")->where("s.isdel=0 and j.isdel=0 and n.isdel=0 and j.provice={$fenqu[$i]["id"]} and n.iskey=1")->count();
            $fenqu[$i]["dgnet"]=db("cg_system s")->join("cg_jxs j","s.jxs=j.id")->join("cg_net n","n.system_id=s.id")->where("s.isdel=0 and j.isdel=0 and n.isdel=0 and j.provice={$fenqu[$i]["id"]} and n.guide=1")->count();
            $fenqu[$i]["othernet"]=db("cg_system s")->join("cg_jxs j","s.jxs=j.id")->join("cg_net n","n.system_id=s.id")->where("s.isdel=0 and j.isdel=0 and n.isdel=0 and j.provice={$fenqu[$i]["id"]} and (n.guide=0 or n.guide is null) and (n.iskey=0 or n.iskey is null)")->count();
        }
        $this->assign("fenqu",$fenqu);
        return $this->fetch();
    }
    public function shengkhdc(){
        $fenqu=Db::table("cg_market_provice")->where(["isdel"=>0])->field("id,title")->select();
        for($i=0;$i<count($fenqu);$i++){
            $fenqu[$i]["importjxs"]=db("cg_jxs")->where("isdel=0 and provice={$fenqu[$i]["id"]} and import=1")->count();
            $fenqu[$i]["alljxs"]=db("cg_jxs")->where("isdel=0 and provice={$fenqu[$i]["id"]}")->count();
            $fenqu[$i]["otherjxs"]=db("cg_jxs")->where("isdel=0 and provice={$fenqu[$i]["id"]} and import=0")->count();
            $fenqu[$i]["system"]=db("cg_system s")->join("cg_jxs j","s.jxs=j.id")->where("s.isdel=0 and j.isdel=0 and j.provice={$fenqu[$i]["id"]}")->count();
            $fenqu[$i]["allnet"]=db("cg_system s")->join("cg_jxs j","s.jxs=j.id")->join("cg_net n","n.system_id=s.id")->where("s.isdel=0 and j.isdel=0 and n.isdel=0 and j.provice={$fenqu[$i]["id"]}")->count();
            $fenqu[$i]["keynet"]=db("cg_system s")->join("cg_jxs j","s.jxs=j.id")->join("cg_net n","n.system_id=s.id")->where("s.isdel=0 and j.isdel=0 and n.isdel=0 and j.provice={$fenqu[$i]["id"]} and n.iskey=1")->count();
            $fenqu[$i]["dgnet"]=db("cg_system s")->join("cg_jxs j","s.jxs=j.id")->join("cg_net n","n.system_id=s.id")->where("s.isdel=0 and j.isdel=0 and n.isdel=0 and j.provice={$fenqu[$i]["id"]} and n.guide=1")->count();
            $fenqu[$i]["othernet"]=db("cg_system s")->join("cg_jxs j","s.jxs=j.id")->join("cg_net n","n.system_id=s.id")->where("s.isdel=0 and j.isdel=0 and n.isdel=0 and j.provice={$fenqu[$i]["id"]} and (n.guide=0 or n.guide is null) and (n.iskey=0 or n.iskey is null)")->count();
        }
         $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();

        Vendor("PHPExcel");


        Vendor("PHPExcel.Writer.Excel5");

        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new PHPExcel();


        $objProps = $objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("省份客户情况报表");

        #################总标题

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:J1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "省份客户情况报表");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
        #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:J2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "(本表单由软件导出，导出时间：" . date("Y-m-d H:i:s") . "；导出人：{$users["em_name"]})");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
        $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->getStyle('4')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:J4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:J4')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:J4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:J4')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->mergeCells('A3:A4');
        $objPHPExcel->getActiveSheet()->setCellValue('A3', "省份");
        $objPHPExcel->getActiveSheet()->mergeCells('B3:D3');
        $objPHPExcel->getActiveSheet()->setCellValue('B3', "经销商");
        $objPHPExcel->getActiveSheet()->mergeCells('E3:F3');
        $objPHPExcel->getActiveSheet()->setCellValue('E3', "系统");
        $objPHPExcel->getActiveSheet()->mergeCells('G3:J3');
        $objPHPExcel->getActiveSheet()->setCellValue('G3', "网点");
        $objPHPExcel->getActiveSheet()->setCellValue('B4', "重点经销商数量");
        $objPHPExcel->getActiveSheet()->setCellValue('C4', "其他经销商数量");
        $objPHPExcel->getActiveSheet()->setCellValue('D4', "经销商数量");
        $objPHPExcel->getActiveSheet()->setCellValue('E4', "经销商总数");
        $objPHPExcel->getActiveSheet()->setCellValue('F4', "系统总数");
        $objPHPExcel->getActiveSheet()->setCellValue('G4', "重点网点数量");
        $objPHPExcel->getActiveSheet()->setCellValue('H4', "导购网点数量");
        $objPHPExcel->getActiveSheet()->setCellValue('I4', "其他网点数量");
        $objPHPExcel->getActiveSheet()->setCellValue('J4', "网点数量");
        $h=4;
        $apply=$fenqu;$he=[0,0,0,0,0,0,0,0,0,0];
        for($i=0;$i<count($fenqu);$i++){
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$apply[$i]["title"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$apply[$i]["importjxs"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$apply[$i]["otherjxs"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$apply[$i]["alljxs"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$apply[$i]["system"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$apply[$i]["system"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$apply[$i]["keynet"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$apply[$i]["dgnet"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$apply[$i]["othernet"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$apply[$i]["allnet"],PHPExcel_Cell_DataType::TYPE_STRING);
            $he[0]+=$apply[$i]["importjxs"];
            $he[1]+=$apply[$i]["otherjxs"];
            $he[2]+=$apply[$i]["alljxs"];
            $he[3]+=$apply[$i]["system"];
            $he[4]+=$apply[$i]["system"];
            $he[5]+=$apply[$i]["keynet"];
            $he[6]+=$apply[$i]["dgnet"];
            $he[7]+=$apply[$i]["othernet"];
            $he[8]+=$apply[$i]["allnet"];
        }
        if(count($fenqu)){
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,"总计",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$he[0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$he[1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$he[2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$he[3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$he[4],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$he[5],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$he[6],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$he[7],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$he[8],PHPExcel_Cell_DataType::TYPE_STRING);
        }
         $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:K'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
           
           
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }
    //省营销费用
    public function shengyx($cid=''){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $fenqu=db("cg_market_provice")->where("isdel=0")->select();
        $this->assign("fenqu",$fenqu);
        $kemu=db("cg_feiyongkemu3")->where("esuseway like '%\"1\"%' or esuseway like '%\"2\"%' or esuseway like '%\"3\"%' ")->select();
        $this->assign("kemu",$kemu);
        return $this->fetch();
    }
    //省查询
    public function query(){
        $request = Request::instance();
        $data = $request->param();
       // print_r($data);
        $cainian=db("cg_cainian")->where(["id"=>$data["cid"]])->find();
        $this->assign("cid",$data["cid"]);
        $this->assign("cainian",$cainian);
        $kaohe2=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->where("k1.cid={$data["cid"]}")->order("k2.starttime")
        ->field("k1.id as k1id,k1.starttime as k1start,k1.endtime as k1end,k2.id,k2.title,k2.starttime,k2.endtime")->select();
        $this->assign("kaohe2",$kaohe2);
        $fid=implode(',',$data["sheng"]);
        $farr=db('cg_market_provice')->where("id in ($fid)")->field("id,title")->select();

        if($data["fx_kind"]==2){
            $kid=implode(',',$data["kemu"]);
            $kidstr=implode('-',$data["kemu"]);
            $kemu=db("cg_feiyongkemu3")->where("id in ($kid)")->select();
        }else{
            $kemu=db("cg_feiyongkemu3")->where("esuseway like '%\"1\"%' or esuseway like '%\"2\"%' or esuseway like '%\"3\"%' ")->select();
            $karr=[];
            foreach ($kemu as $key => $value) {
                # code...
                $karr[]=$value["id"];
            }
            $kidstr=implode('-',$karr);
        }
        $this->assign("kid",$kidstr);
        $fidstr=implode('-',$data["sheng"]);
        $this->assign("fid",$fidstr);
        $where="t.status=7 and (t.status2 is null or t.status2=0) ";
        for($i=0;$i<count($farr);$i++){
            $farr[$i]["ckemu"]=count($kemu);
            $farr[$i]["kemu"]=[];
            for($j=0;$j<count($kemu);$j++){
                $farr[$i]["kemu"][$j]["title"]=$kemu[$j]["title"];
                $farr[$i]["kemu"][$j]["jine"]=[0,0,0,0,0,0,0,0,0,0,0,0];
                $farr[$i]["kemu"][$j]["zjine"]=0;
                for($k=0;$k<count($kaohe2);$k++){
                    $start=$kaohe2[$k]["starttime"];
                    $end=$kaohe2[$k]["endtime"]+3600*24;
                    $tmpjine=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")->join("cg_market_provice z","z.id=j.provice")
                    ->where($where."and z.id={$farr[$i]["id"]} and td.feetype={$kemu[$j]["id"]} and td.start>=$start and td.start<$end")->field("yjsjtrfy,fb_shijitourufeiyong")->select();
                   // print_r($tmpjine);
                    foreach ($tmpjine as $key => $v) {
                        # code...
                        if($v["fb_shijitourufeiyong"]){
                            $farr[$i]["kemu"][$j]["jine"][$k]+=$v["fb_shijitourufeiyong"];
                            $farr[$i]["kemu"][$j]["zjine"]+=$v["fb_shijitourufeiyong"];
                        }else{
                            $farr[$i]["kemu"][$j]["jine"][$k]+=$v["yjsjtrfy"];
                            $farr[$i]["kemu"][$j]["zjine"]+=$v["yjsjtrfy"];
                        }
                    }
                }
                $farr[$i]["kemu"][$j]["avg"]=round($farr[$i]["kemu"][$j]["zjine"]/4,2);
            }
        }
        $this->assign("farr",$farr);
        return $this->fetch();
    }
    public function bingtu($fid,$kid,$cid){
        $sheng=db("cg_market_provice")->where("id=$fid")->value("title");
        $this->assign("sheng",$sheng);
        $cainian=db("cg_cainian")->where(["id"=>$cid])->find();
        $kaohe2=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->where("k1.cid={$cid}")->order("k2.starttime")
        ->field("k1.id as k1id,k1.starttime as k1start,k1.endtime as k1end,k2.id,k2.title,k2.starttime,k2.endtime")->select();
        $this->assign("cainian",$cainian);
        $karr=explode('-',$kid);
        $kid=implode(',',$karr);
        $kemu=db("cg_feiyongkemu3")->where("id in ($kid)")->field("id,title")->select();
        $where="t.status=7 and (t.status2 is null or t.status2=0) ";
        $key=0;
        for($i=0;$i<count($kaohe2);$i++){
           
            $start=$kaohe2[$i]["starttime"];
            $end=$kaohe2[$i]["endtime"]+3600*24;
            $kaohe2[$i]["zonge"]=0;
            $kaohe2[$i]["kemu"]=$kemu;
            for($j=0;$j<count($kemu);$j++){
                $kaohe2[$i]["kemu"][$j]["jine"]=0;
                $tmpjine=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")->join("cg_market_provice z","z.id=j.provice")
                    ->where($where."and z.id={$fid} and td.feetype={$kemu[$j]["id"]} and td.start>=$start and td.start<$end")->field("yjsjtrfy,fb_shijitourufeiyong")->select();
                 foreach ($tmpjine as $k => $v) {
                        # code...
                        if($v["fb_shijitourufeiyong"]){
                            $kaohe2[$i]["zonge"]+=round($v["fb_shijitourufeiyong"]);
                            $kaohe2[$i]["kemu"][$j]["jine"]+=round($v["fb_shijitourufeiyong"]);
                        }else{
                            $kaohe2[$i]["zonge"]+=round($v["yjsjtrfy"]);
                            $kaohe2[$i]["kemu"][$j]["jine"]+=round($v["yjsjtrfy"]);
                        }
                    }    
            }
            if($kaohe2[$i]["zonge"]>0){
                $kaohe2[$i]["key"]=$key; $key++;
            }
        }
        $this->assign("kaohe2",$kaohe2);
       // print_r($kaohe2);
        return $this->fetch();
    }
    public function daochuqu($fid,$kid,$cid){
         $cainian=db("cg_cainian")->where(["id"=>$cid])->find();
         $kaohe2=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->where("k1.cid={$cid}")->order("k2.starttime")
        ->field("k1.id as k1id,k1.starttime as k1start,k1.endtime as k1end,k2.id,k2.title,k2.starttime,k2.endtime")->select();
        $fid=implode(',',explode('-',$fid));
        $farr=db('cg_market_provice')->where("id in ($fid)")->field("id,title")->select();
        $kid=implode(',',explode('-',$kid));
        $kemu=db("cg_feiyongkemu3")->where("id in ($kid)")->select();
        $where="t.status=7 and (t.status2 is null or t.status2=0) ";

        for($i=0;$i<count($farr);$i++){
            $farr[$i]["ckemu"]=count($kemu);
            $farr[$i]["kemu"]=[];
            for($j=0;$j<count($kemu);$j++){
                $farr[$i]["kemu"][$j]["title"]=$kemu[$j]["title"];
                $farr[$i]["kemu"][$j]["jine"]=[0,0,0,0,0,0,0,0,0,0,0,0];
                $farr[$i]["kemu"][$j]["zjine"]=0;
                for($k=0;$k<count($kaohe2);$k++){
                    $start=$kaohe2[$k]["starttime"];
                    $end=$kaohe2[$k]["endtime"]+3600*24;
                    $tmpjine=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")->join("cg_market_provice z","z.id=j.provice")
                    ->where($where."and z.id={$farr[$i]["id"]} and td.feetype={$kemu[$j]["id"]} and td.start>=$start and td.start<$end")->field("yjsjtrfy,fb_shijitourufeiyong")->select();
                    foreach ($tmpjine as $key => $v) {
                        # code...
                        if($v["fb_shijitourufeiyong"]){
                            $farr[$i]["kemu"][$j]["jine"][$k]+=$v["fb_shijitourufeiyong"];
                            $farr[$i]["kemu"][$j]["zjine"]+=$v["fb_shijitourufeiyong"];
                        }else{
                            $farr[$i]["kemu"][$j]["jine"][$k]+=$v["yjsjtrfy"];
                            $farr[$i]["kemu"][$j]["zjine"]+=$v["yjsjtrfy"];
                        }
                    }
                }
                $farr[$i]["kemu"][$j]["avg"]=round($farr[$i]["kemu"][$j]["zjine"]/4,2);
            }
        }
        $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();

        Vendor("PHPExcel");


        Vendor("PHPExcel.Writer.Excel5");

        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new PHPExcel();


        $objProps = $objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("分区营销费用报表");

        #################总标题

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:P1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "分区营销费用报表");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
        #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:P2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "(本表单由软件导出，导出时间：" . date("Y-m-d H:i:s") . "；导出人：{$users["em_name"]})");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"省份");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"科目");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"1月");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"2月");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"3月");
        $objPHPExcel->getActiveSheet()->setCellValue('F3',"4月");
        $objPHPExcel->getActiveSheet()->setCellValue('G3',"5月");
        $objPHPExcel->getActiveSheet()->setCellValue('H3',"6月");
        $objPHPExcel->getActiveSheet()->setCellValue('I3',"7月");
        $objPHPExcel->getActiveSheet()->setCellValue('J3',"8月");
        $objPHPExcel->getActiveSheet()->setCellValue('K3',"9月");
        $objPHPExcel->getActiveSheet()->setCellValue('L3',"10月");
        $objPHPExcel->getActiveSheet()->setCellValue('M3',"11月");
        $objPHPExcel->getActiveSheet()->setCellValue('N3',"12月");
        $objPHPExcel->getActiveSheet()->setCellValue('O3',"平均");
        $objPHPExcel->getActiveSheet()->setCellValue('P3',"小计");
        $h=3;
         for($i=0;$i<count($farr);$i++){
            $h++;$endh=$h+$farr[$i]["ckemu"]-1;
            if($farr[$i]["ckemu"]>1){
                $objPHPExcel->getActiveSheet()->mergeCells('A'.$h.':A'.$endh);
            }
            
             //垂直剧中
            $objPHPExcel->getActiveSheet()->getStyle('A'.$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$farr[$i]["title"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$farr[$i]["kemu"][0]["title"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$farr[$i]["kemu"][0]["jine"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$farr[$i]["kemu"][0]["jine"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$farr[$i]["kemu"][0]["jine"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$farr[$i]["kemu"][0]["jine"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$farr[$i]["kemu"][0]["jine"][4],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$farr[$i]["kemu"][0]["jine"][5],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$farr[$i]["kemu"][0]["jine"][6],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$farr[$i]["kemu"][0]["jine"][7],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$farr[$i]["kemu"][0]["jine"][8],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$farr[$i]["kemu"][0]["jine"][9],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$farr[$i]["kemu"][0]["jine"][10],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$farr[$i]["kemu"][0]["jine"][11],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$farr[$i]["kemu"][0]["avg"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$farr[$i]["kemu"][0]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
            for($j=1;$j<count($kemu);$j++){
                $h++;
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$farr[$i]["kemu"][$j]["title"],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$farr[$i]["kemu"][$j]["jine"][0],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$farr[$i]["kemu"][$j]["jine"][1],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$farr[$i]["kemu"][$j]["jine"][2],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$farr[$i]["kemu"][$j]["jine"][3],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$farr[$i]["kemu"][$j]["jine"][4],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$farr[$i]["kemu"][$j]["jine"][5],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$farr[$i]["kemu"][$j]["jine"][6],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$farr[$i]["kemu"][$j]["jine"][7],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$farr[$i]["kemu"][$j]["jine"][8],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$farr[$i]["kemu"][$j]["jine"][9],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$farr[$i]["kemu"][$j]["jine"][10],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$farr[$i]["kemu"][$j]["jine"][11],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$farr[$i]["kemu"][$j]["avg"],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$farr[$i]["kemu"][$j]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
            }
        }
         $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:P'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(20);
           
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');  
    }
}